回復 1#leiru
請測試看看,謝謝
Sub test()
Dim Arr, xD, i&, T$
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([a1], [b65536].End(3))
For i = 2 To UBound(Arr)
T = Arr(i, 1): If T = "" Then GoTo 99
If xD.Exists(T) Then
xD(T) = xD(T) & "、" & Arr(i, 2)
Else
xD(T) = Arr(i, 2)
End If
99: Next i
Range("d2").Resize(xD.Count, 1) = Application.Transpose(xD.keys)
Range("e2").Resize(xD.Count, 1) = Application.Transpose(xD.Items)
End Sub作者: Andy2483 時間: 2021-10-14 12:00
Sub test2()
Dim Arr, Brr(), xD, T$, k, TC%, TC1%, R%, C%
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([a1], [b65536].End(3))
For i = 2 To UBound(Arr)
T = Arr(i, 1): If T = "" Then GoTo 99
If xD.Exists(T) Then
xD(T) = xD(T) & "、" & Arr(i, 2)
Else
xD(T) = Arr(i, 2)
End If
99: Next i
ReDim Brr(1 To xD.Count, 1 To UBound(Arr))
R = 1
For Each k In xD.keys
xD(k) = Split(xD(k), "、")
TC = UBound(xD(k)) + 2
If TC > TC1 Then TC1 = TC
Brr(R, 1) = k
For C = 2 To UBound(xD(k)) + 2
Brr(R, C) = xD(k)(C - 2)
Next
R = R + 1
Next
Range("g2").Resize(R - 1, TC1) = Brr
End Sub作者: Andy2483 時間: 2021-10-14 16:47
Sub test2_1()
Dim Arr, Brr(), xD, T$, k, MA%, R%, C%
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([a1], [b65536].End(3))
For i = 2 To UBound(Arr)
T = Arr(i, 1): If T = "" Then GoTo 99
xD(T) = xD(T) + 1
99: Next i
MA = WorksheetFunction.Max(xD.Items)
ReDim Brr(0 To xD.Count, 1 To MA + 1)
i = 0
For Each k In xD.keys
Brr(i, 1) = k
R = 2
For C = 2 To UBound(Arr)
If Arr(C, 1) = Brr(i, 1) Then
Brr(i, R) = Arr(C, 2)
R = R + 1
End If
Next
i = i + 1
Next
Range("g2").Resize(xD.Count, MA + 1) = Brr
End Sub作者: 准提部林 時間: 2021-10-17 10:53
兩欄式:
Sub test_01()
Dim Arr, xD, i&, T$, T2$, R&, N&
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([a1], [b65536].End(3))
For i = 2 To UBound(Arr)
T = Arr(i, 1): T2 = Arr(i, 2): R = xD(T)
If T = "" Or T2 = "" Then GoTo 99
If R > 0 Then Arr(R, 2) = Arr(R, 2) & "、" & T2: GoTo 99
N = N + 1: R = N + 1: xD(T) = R
Arr(R, 1) = Arr(i, 1): Arr(R, 2) = T2
99: Next i
Range("d1").Resize(N + 1, 2) = Arr
End Sub作者: 准提部林 時間: 2021-10-17 10:54
多欄式:
Sub test_02()
Dim Arr, Brr, xD, i&, T$, T2$, R&, C%, Cx%, N&
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([a1], [b65536].End(3))
ReDim Brr(1 To UBound(Arr), 1 To 200)
For i = 2 To UBound(Arr)
T = Arr(i, 1): T2 = Arr(i, 2)
If T = "" Or T2 = "" Then GoTo 99
R = xD(T): C = xD(T & "/c")
If R = 0 Then N = N + 1: R = N + 1: xD(T) = R: Brr(R, 1) = Arr(i, 1)
C = C + 1: xD(T & "/c") = C: Brr(R, C + 1) = T2
If C > Cx Then Cx = C: Brr(1, Cx + 1) = "訂單(" & Cx & ")"
99: Next i
Brr(1, 1) = "發票號碼"
Range("g1").Resize(N + 1, Cx + 1) = Brr
End Sub作者: Andy2483 時間: 2023-1-5 16:47