|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Option Explicit
Sub test()
Dim d, arr, s$, i&, j&, k, t, m&
Set d = CreateObject("Scripting.Dictionary")
arr = Sheets("Sheet1").[a1].CurrentRegion.Value
ReDim brr(1 To UBound(arr), 1 To UBound(arr, 2) + 1)
For i = 2 To UBound(arr)
If InStr(arr(i, 2), "-") > 0 Then
arr(i, 2) = Trim(Left(arr(i, 2), InStr(arr(i, 2), "-") - 1))
End If
s = arr(i, 1) & Trim(arr(i, 2))
If Not d.exists(s) Then
d(s) = Array(arr(i, 1), arr(i, 2), arr(i, 3), 1)
Else
t = d(s)
t(2) = t(2) + arr(i, 3)
t(3) = t(3) + 1
d(s) = t
End If
Next
m = 1
brr(m, 1) = "工序": brr(m, 2) = "单号": brr(m, 3) = "数量": brr(m, 4) = "次数"
For Each k In d.items
If k(3) > 1 Then
m = m + 1
For j = 0 To 3
brr(m, j + 1) = k(j)
Next
End If
Next
With Sheets("2")
.[a11].CurrentRegion.ClearContents
If m > 0 Then .[a11].Resize(m, 4) = brr
End With
End Sub |
|