Sub i()
Dim i, d, ar, br(1 To 65536, 1 To 300)
Set d = CreateObject("scripting.dictionary")
ar = Range("a2:h" & Cells(Rows.Count, 1).End(3).Row)
For i = 1 To UBound(ar, 1)
If ar(i, 8) > "" Then
If d.Exists(ar(i, 1) & ar(i, 2) & ar(i, 3)) Then
s = ar(i, 8) * 6 - 2
m = d(ar(i, 1) & ar(i, 2) & ar(i, 3))
Do
If br(m, s) <> "" Then
s = s + 6
Else
Exit Do
End If
Loop While br(d(ar(i, 1) & ar(i, 2) & ar(i, 3)), s) = ""
br(m, s) = ar(i, 8)
br(m, s + 1) = ar(i, 5)
br(m, s + 2) = ar(i, 6)
br(m, s + 3) = ar(i, 7)
If s > n Then n = s
Else
k = k + 1
d(ar(i, 1) & ar(i, 2) & ar(i, 3)) = k
s = ar(i, 8) * 6 - 2
br(k, 1) = ar(i, 1)
br(k, 2) = ar(i, 2)
br(k, 3) = ar(i, 3)
br(k, s) = ar(i, 8)
br(k, s + 1) = ar(i, 5)
br(k, s + 2) = ar(i, 6)
br(k, s + 3) = ar(i, 7)
If s > n Then n = s
End If
End If
Next
[j30].Resize(k, n + 5) = br
End Sub
写的不好莫怪 |