|
写了一个:
Sub test1()
Dim arr(), brr()
i% = Sheets("表一").Range("A65536").End(3).Row
arr = Sheets("表一").Range("A3").Resize(i - 2, 2).Value
ReDim Preserve arr(1 To i - 2, 1 To i)
i% = Sheets("表二").Range("A65536").End(3).Row
brr = Sheets("表二").Range("A2").Resize(i - 1, 1).Value
Set d = CreateObject("scripting.dictionary")
For i = 1 To UBound(brr)
d(brr(i, 1)) = 0
brr(i, 1) = 1
Next
For i = 1 To UBound(arr)
If d.exists(arr(i, 1)) Then
If d(arr(i, 1)) < 1 Then
j = j% + 1
d(arr(i, 1)) = j
arr(d(arr(i, 1)), 1) = arr(i, 1)
End If
brr(d(arr(i, 1)), 1) = brr(d(arr(i, 1)), 1) + 1
arr(d(arr(i, 1)), brr(d(arr(i, 1)), 1)) = arr(i, 2)
If maxa < brr(d(arr(i, 1)), 1) Then maxa = brr(d(arr(i, 1)), 1)
End If
Next
Sheets("表三").Range("A2:Z65536").ClearContents
Sheets("表三").Range("A2").Resize(j, maxa) = arr
End Sub |
|