Sub test()
Set d = CreateObject("scripting.dictionary")
ar = Sheets("原数据表").[a1].CurrentRegion
ReDim br(1 To UBound(ar), 1 To UBound(ar, 2))
For i = 2 To UBound(ar)
If Trim(ar(i, 1)) <> "" And Trim(ar(i, 2)) <> "" And Trim(ar(i, 3)) <> "" And Trim(ar(i, 4)) <> "" Then
s = Trim(ar(i, 1)) & "|" & Trim(ar(i, 2)) & "|" & Trim(ar(i, 3)) & "|" & Trim(ar(i, 4))
t = d(s)
If t = "" Then
k = k + 1
d(s) = k
t = k
For j = 1 To 6
br(k, j) = ar(i, j)
Next j
br(k, 8) = ar(i, 8)
End If
br(t, 7) = br(t, 7) + ar(i, 7)
If Trim(ar(i, 9)) <> "" Then
If br(t, 9) = "" Then
br(t, 9) = ar(i, 9)
Else
br(t, 9) = br(t, 9) & "," & ar(i, 9)
End If
End If
End If
Next i
With Sheets("处理后结果")
.[a1].CurrentRegion.Offset(1) = Empty
.[a2].Resize(k, UBound(br, 2)) = br
End With
End Sub
|