Sub kk()
Dim dic As Object
Set dic = CreateObject("scripting.dictionary")
With Sheet1
irow = .Cells(Rows.Count, 1).End(3).Row
arr = .Range("a3:g" & irow)
ReDim brr(1 To UBound(arr), 1 To 7)
For i = 1 To UBound(arr)
If dic.exists(arr(i, 3)) = False Then
n = n + 1
dic(arr(i, 3)) = n
brr(n, 1) = n
brr(n, 2) = arr(i, 2)
brr(n, 3) = arr(i, 3)
brr(n, 4) = arr(i, 4)
brr(n, 5) = arr(i, 5)
brr(n, 6) = arr(i, 6) & arr(i, 7)
brr(n, 7) = arr(i, 7)
Else
k = dic(arr(i, 3))
brr(k, 6) = brr(k, 6) & Chr(10) & arr(i, 6) & arr(i, 7)
brr(k, 7) = brr(k, 7) + arr(i, 7)
End If
Next i
.Range("j9").Resize(n, 7) = brr
End With
End Sub
|