'条件不够,按整体同列来处理的,应该是按区域来同列的,但区域分块不明显,,,
Option Explicit
Sub test()
Dim arr, i, j, n, dic
Set dic = CreateObject("scripting.dictionary")
arr = Range("a2:k" & Cells(Rows.Count, "a").End(xlUp).Row)
ReDim brr(1 To UBound(arr, 1), 1 To 50 * UBound(arr, 2)) As String
For i = 1 To UBound(arr, 1) - 1
For j = 1 To UBound(arr, 2)
If Len(arr(i, j)) Then
If dic.exists(arr(i, j)) = False Then n = n + 1: dic(arr(i, j)) = n
brr(i, dic(arr(i, j))) = arr(i, j)
End If
Next
Next
[l2].Resize(UBound(brr, 1), n) = brr
End Sub |