|
Sub dsmch()
Dim arr, brr, crr, d, d2, i&, zf, s&, s2&
Set d = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")
arr = Sheets("交飞明细").Range("A1").CurrentRegion
ReDim brr(1 To UBound(arr), 1 To 4)
For i = 2 To UBound(arr)
arr(i, 2) = Right(String(8, "0") & arr(i, 2), 8) '8位数的工号足够,根据需要取多个!
zf = arr(i, 2) & "," & arr(i, 11) '工号,制单号!
If Not d.Exists(zf) Then
s = s + 1
d(zf) = s
brr(s, 1) = arr(i, 2)
brr(s, 2) = arr(i, 11)
brr(s, 3) = arr(i, 8)
brr(s, 4) = arr(i, 14)
Else
n = d(zf)
If InStr(brr(n, 3), arr(i, 8)) = 0 Then brr(n, 3) = brr(n, 3) & "\" & arr(i, 8)
brr(n, 4) = brr(n, 4) + arr(i, 14)
End If
Next
ReDim crr(1 To s, 1 To 200)
d.RemoveAll
For i = 1 To s
d2(brr(i, 1)) = d2(brr(i, 1)) + 1
If InStr(brr(i, 3), "\") Then brr(i, 3) = "(" & brr(i, 3) & ")"
zf = brr(i, 3) & " " & brr(i, 2) & " " & brr(i, 4) '当只有一个制时不用括号,二个以上制单要括号,怎么表示?
If Not d.Exists(brr(i, 1)) Then
s2 = s2 + 1
d(brr(i, 1)) = s2
crr(s2, 1) = brr(i, 1)
crr(s2, d2(brr(i, 1)) + 1) = zf
Else
crr(d(brr(i, 1)), d2(brr(i, 1)) + 1) = zf
End If
Next
Cells.ClearContents
Range("a10").Resize(s2, Application.Max(d2.Items) + 1) = crr
Columns.AutoFit
End Sub
|
|