|
- Sub 字典的项去重复后求和参考()
- Set d = CreateObject("scripting.dictionary")
- arr = Range("F1").CurrentRegion.Offset(1, 0)
- For i = 2 To UBound(arr)
- S = arr(i, 1) & "," & arr(i, 2) & "," & arr(i, 3)
- If InStr(d(S), arr(i, 4)) = 0 Then
- d(S) = d(S) & "|" & arr(i, 4)
- End If
- Next
- k = d.keys
- t = d.items
- ReDim brr(0 To d.Count - 1, 1 To 10)
- For i = 0 To d.Count - 1
- brr(i, 1) = Split(k(i), ",")(0)
- brr(i, 2) = Split(k(i), ",")(1)
- tt = Split(Mid$(t(i), 2), "|")
- For c = 0 To UBound(tt)
- If InStr(t(i), "|") = 0 Then
- brr(i, 3) = tt(c)
- Else
- brr(i, 3) = brr(i, 3) + Val(tt(c))
- End If
- Next c
- Next i
- drr = Range("A1").CurrentRegion.Offset(2, 0)
- ReDim crr(1 To UBound(drr), 1 To UBound(drr, 2))
- For i = 1 To UBound(drr)
- For j = 0 To UBound(brr)
- If drr(i, 1) = brr(j, 1) And drr(i, 2) = brr(j, 2) And brr(j, 3) > drr(i, 3) Then
- n = n + 1
- For c = 1 To UBound(drr, 2)
- crr(n, c) = brr(j, c)
- Next c
- End If
- Next j
- Next i
- ReDim Err(1 To UBound(arr), 1 To UBound(arr, 2))
- For y = 2 To UBound(arr)
- For x = 1 To UBound(crr)
- If Len(crr(x, 1)) Then
- If InStr(arr(y, 1), crr(x, 1)) And InStr(arr(y, 2), crr(x, 2)) And arr(y, 3) = "下料" Then
- m = m + 1
- For Z = 1 To UBound(arr, 2)
- Err(m, Z) = arr(y, Z)
- Next Z
- End If
- End If
- Next x
- Next y
- Sheet2.Cells.Clear
- Sheet2.[A1].Resize(UBound(Err), UBound(Err, 2)) = Err
- End Sub
复制代码 |
|