Macro to extract repeating invoice numbers and details

[Omar M]

Hi ,
I search for macro to merge data in OUT sheet for each INVOICE NO for each sheet .
should merge amounts in TOTAL column (H) based on duplicates INVOICE NO in column C ,CONDITION in column D for each sheet alone and exclude columns E:G from merging .
the result should be in OUT sheet from row2 and should show sheet name for each sheet in column E where the INVOICE NO is existed.
this is small data in sample but I would deal with 9500 rows for each sheet.

INVV.xlsm
	A	B	C	D	E	F	G	H
1	ITEM	DATE	INVOICE NO	CONDITION	ID	QTY	UNIT PRICE	TOTAL
2	1	01/11/2023	SSLL-001	PAID	CSDFA1	22.00	122.00	2,684.00
3	2	01/11/2023	SSLL-001	PAID	CSDFA2	11.00	125.00	1,375.00
4	3	01/11/2023	SSLL-001	PAID	CSDFA3	456.00	123.00	56,088.00
5	4	04/11/2023	SSLL-002	NOT PAID	CSDFA4	66.00	144.00	9,504.00
6	5	05/11/2023	SSLL-003	NOT PAID	CSDFA5	55.00	133.00	7,315.00
7	6	06/11/2023	SSLL-004	NOT PAID	CSDFA6	222.00	144.00	31,968.00
8	7	06/11/2023	SSLL-004	NOT PAID	CSDFA7	45.00	112.00	5,040.00
9	8	06/11/2023	SSLL-004	NOT PAID	CSDFA8	133.00	122.00	16,226.00
10	9	09/11/2023	SSLL-005	NOT PAID	CSDFA9	34.00	109.00	3,706.00
11	10	10/11/2023	SSLL-006	PAID	CSDFA10	12.00	122.00	1,464.00
12	11	10/11/2023	SSLL-006	PAID	CSDFA11	12.00	120.00	1,440.00
13	12	12/11/2023	SSLL-007	CASH BY SAFE	CSDFA11	12.00	120.00	1,440.00
14	13	12/11/2023	SSLL-007	CASH BY SAFE	CSDFA12	12.00	120.00	1,440.00
15	14	13/11/2023	SSLL-008	CASH BY BANK	CSDFA8	133.00	122.00	16,226.00
16	15	13/11/2023	SSLL-008	CASH BY BANK	CSDFA9	133.00	122.00	16,226.00
SL

Cell Formulas
Range	Formula
H2:H16	H2	=F2*G2

INVV.xlsm
	A	B	C	D	E	F	G	H
1	ITEM	DATE	INVOICE NO	CONDITION	ID	QTY	UNIT PRICE	TOTAL
2	1	15/01/2023	MMLLL-001	PAID	CSDFA9	2.00	110.00	220.00
3	2	15/01/2023	MMLLL-001	PAID	CSDFA10	11.00	102.00	1,122.00
4	3	15/01/2023	MMLLL-002	PAID	CSDFA3	6.00	121.00	726.00
5	4	15/01/2023	MMLLL-002	PAID	CSDFA4	6.00	141.00	846.00
6	5	15/01/2023	MMLLL-002	PAID	CSDFA5	5.00	102.00	510.00
7	6	16/01/2023	MMLLL-003	NOT PAID	CSDFA6	11.00	122.00	1,342.00
8	7	06/11/2023	MMLLL-003	NOT PAID	CSDFA7	10.00	100.00	1,000.00
9	8	06/11/2023	MMLLL-003	NOT PAID	CSDFA8	13.00	112.00	1,456.00
10	9	06/11/2023	MMLLL-004	CASH BY BANK	CSDFA9	23.00	100.00	2,300.00
11	10	06/11/2023	MMLLL-004	CASH BY BANK	CSDFA10	10.00	90.00	900.00
12	11	07/11/2023	MMLLL-005	CASH BY SAFE	CSDFA5	5.00	102.00	510.00
13	12	07/11/2023	MMLLL-005	CASH BY SAFE	CSDFA5	5.00	102.00	510.00
ML

Cell Formulas
Range	Formula
H2:H13	H2	=F2*G2

INVV.xlsm
	A	B	C	D	E	F	G	H
1	ITEM	DATE	INVOICE NO	CONDITION	ID	QTY	UNIT PRICE	TOTAL
2	1	17/11/2023	TTLL-001	CASH BY BANK	CSDFA6	22.00	160.00	3,520.00
3	2	17/11/2023	TTLL-001	CASH BY BANK	CSDFA7	22.00	130.00	2,860.00
4	3	17/11/2023	TTLL-001	CASH BY BANK	CSDFA3	44.00	140.00	6,160.00
5	4	17/11/2023	TTLL-002	NOT PAID	CSDFA6	10.00	160.00	1,600.00
6	5	17/11/2023	TTLL-002	NOT PAID	CSDFA7	10.00	130.00	1,300.00
7	6	18/11/2023	TTLL-003	NOT PAID	CSDFA6	10.00	150.00	1,500.00
8	7	18/11/2023	TTLL-004	PAID	CSDFA7	22.00	128.00	2,816.00
9	8	18/11/2023	TTLL-005	PAID	CSDFA8	100.00	150.00	15,000.00
10	9	18/11/2023	TTLL-006	CASH BY SAFE	CSDFA9	30.00	140.00	4,200.00
11	10	18/11/2023	TTLL-006	CASH BY SAFE	CSDFA10	35.00	150.00	5,250.00
TL

Cell Formulas
Range	Formula
H2:H11	H2	=F2*G2

INVV.xlsm
	A	B	C	D	E	F
1	ITEM	DATE	INVOICE NO	CONDITION	INVOICE TYP	TOTAL
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
OUT

result after

INVV.xlsm
	A	B	C	D	E	F
1	ITEM	DATE	INVOICE NO	CONDITION	INVOICE TYP	TOTAL
2	1	01/11/2023	SSLL-001	PAID	SL	60,147.00
3	2	04/11/2023	SSLL-002	NOT PAID	SL	9,504.00
4	3	05/11/2023	SSLL-003	NOT PAID	SL	7,315.00
5	4	06/11/2023	SSLL-004	NOT PAID	SL	53,234.00
6	5	09/11/2023	SSLL-005	NOT PAID	SL	3,706.00
7	6	10/11/2023	SSLL-006	PAID	SL	2,904.00
8	7	12/11/2023	SSLL-007	CASH BY SAFE	SL	2,880.00
9	8	13/11/2023	SSLL-008	CASH BY BANK	SL	32,452.00
10	9	15/01/2023	MMLLL-001	PAID	ML	1,342.00
11	10	15/01/2023	MMLLL-002	PAID	ML	2,082.00
12	11	16/01/2023	MMLLL-003	NOT PAID	ML	3,798.00
13	12	06/11/2023	MMLLL-004	CASH BY BANK	ML	3,200.00
14	13	07/11/2023	MMLLL-005	CASH BY SAFE	ML	1,020.00
15	14	17/11/2023	TTLL-001	CASH BY BANK	TL	12,540.00
16	15	17/11/2023	TTLL-002	NOT PAID	TL	2,900.00
17	16	18/11/2023	TTLL-003	NOT PAID	TL	1,500.00
18	17	18/11/2023	TTLL-004	PAID	TL	2,816.00
19	18	18/11/2023	TTLL-005	PAID	TL	15,000.00
20	19	18/11/2023	TTLL-006	CASH BY SAFE	TL	9,450.00
OUT

I hope to find solution soon.
thanks for everyone see my subject.

 

[DanteAmor]

Try this:

Sub MergeData()
  Dim shs As Variant, a() As Variant, b() As Variant, s As Variant
  Dim dic As Object, ky As String
  Dim i As Long, n As Long, y As Long
  
  shs = Array("SL", "ML", "TL")
  Set dic = CreateObject("SCripting.Dictionary")
  
  For Each s In shs
    Erase a, b
    a = Sheets(s).Range("A2", Sheets(s).Range("H" & Rows.Count).End(3)).Value
    ReDim b(1 To UBound(a, 1), 1 To 6)
    dic.RemoveAll
    y = 0
    
    For i = 1 To UBound(a, 1)
      ky = a(i, 3) & "|" & a(i, 4)
      If Not dic.exists(ky) Then
        y = y + 1
        dic(ky) = y
        n = n + 1
      End If
      y = dic(ky)
      b(y, 1) = n
      b(y, 2) = a(i, 2)
      b(y, 3) = a(i, 3)
      b(y, 4) = a(i, 4)
      b(y, 5) = s
      b(y, 6) = b(y, 6) + a(i, 8)
    Next
    Sheets("OUT").Range("A" & Rows.Count).End(3)(2).Resize(UBound(b, 1), UBound(b, 2)).Value = b
  Next
End Sub

 

[Omar M]

Awesome !
may you adapt the code by clear data in OUT sheet before brings data.
actually I don't repeat copying the same data to the bottom when run the macro every time.
thanks.

 

[DanteAmor]

may you adapt the code by clear data in OUT sheet before brings data.

After this line:

  Set dic = CreateObject("SCripting.Dictionary")

Put this line:

  Sheets("OUT").Range("A2:F" & Rows.Count).ClearContents

 

[Omar M]

Excellent!
many thanks for a great assistance!