Dear all,

Thank you.

[attach]34409[/attach]

[attach]34410[/attach]

Sub test()
Dim Arr, xD, Brr(), T\$, T1\$, i&, n%, m%
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([a1], [c65536].End(3))
ReDim Brr(1 To UBound(Arr), 1 To 3)
For i = 2 To UBound(Arr)
T = Arr(i, 2) & "|" & Arr(i, 3)
T1 = Arr(i, 1) & "|" & Arr(i, 2) & "|" & Arr(i, 3)
If xD.Exists(T) Then
m = xD(T)
If Not xD.Exists(T1) Then Brr(m, 3) = Brr(m, 3) + 1
Else
n = n + 1: xD(T) = n: xD(T1) = n
Brr(n, 1) = Arr(i, 2)
Brr(n, 2) = Arr(i, 3)
Brr(n, 3) = 1
End If
Next
With Range("g2").Resize(n, 3)
.Value = Brr
.Sort Key1:=.Item(1), Order1:=1, _
End With
End Sub

https://blog.xuite.net/hcm19522/twblog/590131891

Dear hcm19522,

=SUMPRODUCT((B\$2:B\$20000&C\$2:C\$20000=G2&H2)/COUNTIFS(A:A,A\$2:A\$20000,B:B,B\$2:B\$20000,C:C,C\$2:C\$20000))

Thank you.

Dear samwang,

Thank you.

Dear samwang,

Sub test()
Dim Arr, xD, Brr(), T\$, T1\$, i&, n%, m%
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([01!a1], [01!c65536].End(3))
ReDim Brr(1 To UBound(Arr), 1 To 3)
For i = 2 To UBound(Arr)
T = Arr(i, 2) & "|" & Arr(i, 3)
T1 = Arr(i, 1) & "|" & Arr(i, 2) & "|" & Arr(i, 3)
If xD.Exists(T) Then
m = xD(T)
If Not xD.Exists(T1) Then Brr(m, 3) = Brr(m, 3) + 1
Else
n = n + 1: xD(T) = n: xD(T1) = n
Brr(n, 1) = Arr(i, 2)
Brr(n, 2) = Arr(i, 3)
Brr(n, 3) = 1
End If
Next

With Range([02!g2]).Resize(n, 3)
.Value = Brr
.Sort Key1:=.Item(1), Order1:=1, _
End With
End Sub

J2 =COUNT(0/FREQUENCY((G2=B\$2:B\$99)*(H2=C\$2:C\$99)*A\$2:A\$99,A\$2-2+ROW(\$1:\$99)))-1

Dear ML089,

Thank you.

Dear ML089,

Thank you.

[attach]34411[/attach]

With Range([02!g2]).Resize(n, 3)
>> With Sheets("02").Range("g2").Resize(n, 3)

Dear samwang,

[attach]34412[/attach]

Sub test()
Dim Arr, xD, Brr(), T\$, T1\$, i&, n%, m%
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([01!a1], [01!c65536].End(3))
ReDim Brr(1 To UBound(Arr), 1 To 3)
For i = 2 To UBound(Arr)
T = Arr(i, 2) & "|" & Arr(i, 3)
T1 = Arr(i, 1) & "|" & Arr(i, 2) & "|" & Arr(i, 3)
If xD.Exists(T) Then
m = xD(T)
If Not xD.Exists(T1) Then Brr(m, 3) = Brr(m, 3) + 1
Else
n = n + 1: xD(T) = n: xD(T1) = n
Brr(n, 1) = Arr(i, 2)
Brr(n, 2) = Arr(i, 3)
Brr(n, 3) = 1
End If
Next

With Sheets("02").Range("g2").Resize(n, 3)
.Value = Brr
.Sort Key1:=.Item(1), Order1:=1, _
End With
End Sub

Dear samwang,

Dear samwang,

2021/1/31  A123456  R001   ---> 這些我都設定同一日期(多列)

[attach]34415[/attach]

2021/1/31  A123456  R001   ---> 這些我都設定同一日期(多列)

>> 不好意思，更新如紅字，請測試看看，謝謝
Sub test2()
Dim Arr, xD, Brr(), T\$, T1\$, i&, n%, m%
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([01!a1], [01!c65536].End(3))
ReDim Brr(1 To UBound(Arr), 1 To 3)
For i = 2 To UBound(Arr)
T = Arr(i, 2) & "|" & Arr(i, 3)
T1 = Arr(i, 1) & "|" & Arr(i, 2) & "|" & Arr(i, 3)
If xD.Exists(T) Then
m = xD(T)
If Not xD.Exists(T1) Then Brr(m, 3) = Brr(m, 3) + 1: xD(T1) = n
Else
n = n + 1: xD(T) = n: xD(T1) = n
Brr(n, 1) = Arr(i, 2)
Brr(n, 2) = Arr(i, 3)
Brr(n, 3) = 1
End If
Next

With Sheets("02").Range("g2").Resize(n, 3)
.Value = Brr
.Sort Key1:=.Item(1), Order1:=1, _
End With
End Sub

Sub test_1()
Dim Arr, xD, Brr(), T\$, T1\$, i&, n%, m%
[02!g:i].ClearContents '不累計, 這要先清空
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([01!a1], [01!c65536].End(3))
ReDim Brr(1 To UBound(Arr), 1 To 3)
For i = 2 To UBound(Arr)
T = Arr(i, 2) & "|" & Arr(i, 3)
T1 = Arr(i, 1) & "|" & T
m = xD(T): xD(T1) = xD(T1) + 1
If m = 0 Then
n = n + 1: m = n: xD(T) = n
Brr(n, 1) = Arr(i, 2): Brr(n, 2) = Arr(i, 3)
End If
If xD(T1) = 1 Then Brr(m, 3) = Brr(m, 3) + 1
Next
[02!g1:i1] = [{"料號","批號","天數"}]
With [02!g2].Resize(n, 3)
.Value = Brr
.Sort Key1:=.Item(1), Order1:=1, _
End With
End Sub

Dear samwang,

Dear 准提部林,

Test OK. 大感謝！！

Dear ML089,

 歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)