|
Sub huizong()
Dim d As Object
Dim ar As Variant
Dim br()
Set d = CreateObject("scripting.dictionary")
ar = Sheet1.[a1].CurrentRegion
ReDim br(1 To UBound(ar), 1 To 5)
For i = 2 To UBound(ar)
If Trim(ar(i, 2)) <> "" Then
t = d(Trim(ar(i, 2)))
If t = "" Then
k = k + 1
d(Trim(ar(i, 2))) = k
t = k
br(k, 1) = k
br(k, 2) = ar(i, 2)
End If
If br(t, 4) = "" Then
br(t, 4) = ar(i, 3)
Else
br(t, 4) = br(t, 4) & "," & ar(i, 3)
End If
br(t, 5) = br(t, 5) + ar(i, 4)
End If
Next i
With Sheet2
.[a1].CurrentRegion.Offset(1) = Empty
.[a2].Resize(k, 5) = br
End With
MsgBox "ok!"
End Sub
|
|