Sub bydic()
Dim d As Object, i%, j%, t, arr, brr
Set d = CreateObject("scripting.dictionary")
arr = Sheet1.[a1].CurrentRegion
brr = Sheet2.[a1].CurrentRegion
For i = 2 To UBound(arr)
For j = 1 To UBound(arr, 2)
d(arr(i, 6)) = d(arr(i, 6)) & "|" & arr(i, j)
Next
d(arr(i, 6)) = Mid(d(arr(i, 6)), 2, 99)
Next
t = d.items
For i = 2 To UBound(brr)
If d.exists(brr(i, 1)) Then
For j = 3 To UBound(brr, 2)
brr(i, j) = Split(t(i - 2), "|")(j - 3)
Next
End If
Next
Sheet2.[a8].Resize(UBound(brr), UBound(brr, 2)) = brr
End Sub |