Sub zz()
Dim d(1 To 4) As New Dictionary, ar
Application.ScreenUpdating = False
ar = Sheet2.Range("A1").CurrentRegion
For i = 2 To UBound(ar)
If Not d(1).Exists(ar(i, 1)) Then
d(1)(ar(i, 1)) = Array(ar(i, 2), ar(i, 4))
Else
d(2)(ar(i, 1)) = Array(ar(i, 2), ar(i, 4))
End If
If ar(i, 4) > d(3)(ar(i, 1)) Then
d(3)(ar(i, 1)) = ar(i, 4)
d(4)(ar(i, 1)) = ar(i, 2)
End If
Next
Sheet1.Activate
For i = 2 To [a65536].End(3).Row
Cells(i, 2).Resize(1, 2) = d(1)(Cells(i, 1).Value)
Cells(i, 5).Resize(1, 2) = d(2)(Cells(i, 1).Value)
Cells(i, 8) = d(4)(Cells(i, 1).Value)
Cells(i, 9) = d(3)(Cells(i, 1).Value)
Next
Application.ScreenUpdating = True
End Sub
|