|
Sub hh()
Dim arr, brr(1 To 10000, 1 To 2)
r = Cells(Rows.Count, 2).End(xlUp).Row
arr = Range("b2:b" & r).Value
Set d = CreateObject("scripting.dictionary")
For i = 1 To UBound(arr)
s = Split(arr(i, 1), " ")
For x = 0 To UBound(s)
If Not d.exists(s(x)) Then
k = k + 1
d(s(x)) = k
brr(k, 1) = s(x)
brr(k, 2) = 1
Else
r = d(s(x))
brr(r, 2) = brr(r, 2) + 1
End If
Next
Next
Dim a(1 To 4, 1 To 2)
For i = 1 To 4
a(i, 1) = i + 4
Next
For i = 1 To 4
For j = 1 To k
If brr(j, 2) = a(i, 1) Then a(i, 2) = a(i, 2) & " " & brr(j, 1)
Next
Next
[d2].Resize(4, 2) = a
[d7].Resize(k, 2) = brr
End Sub |
|