populate summary sheet by merge amounts based on two columns(ID,CUSTOMER)

[Abdo]

Hi
I need to create summary for report in SUMMARY sheet

ABDD1.xlsm
	A	B	C	D	E	F	G	H
1
2		FROM DATE	TO DATE
3
4
5
6	ITEM	CUSTOMER	ID	OPENING	BU	SE	BU TOTAL	SE TOTAL
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Summary

the data in sheets

ABDD1.xlsm
	A	B	C	D	E	F
1	ITEM	CUSTOMER	ID	QTY	PRICE	TOTAL
2	1	ABBD1	CLOTH-100	30.00	480.00	14,400.00
3	2	SAD1	CLOTH-101	50.00	490.00	24,500.00
4	3	SAD2	CLOTH-102	40.00	990.00	39,600.00
5	4	SAD3	CLOTH-103	60.00	1,100.00	66,000.00
6	5	SAD4	CLOTH-104	80.00	1,770.00	141,600.00
7	6	SERW-00	CLOTH-105	100.00	1,680.00	168,000.00
8	7	SERW-01	CLOTH-106	120.00	1,690.00	202,800.00
9	8	SERW-02	CLOTH-107	140.00	580.00	81,200.00
10	9	SERW-03	CLOTH-108	160.00	1,660.00	265,600.00
11	10	SERW-04	CLOTH-109	180.00	1,700.00	306,000.00
12	11	SERW-05	CLOTH-110	200.00	1,600.00	320,000.00
13	12	SERW-06	CLOTH-111	201.00	1,780.00	357,780.00
14	13	ABBD1	CLOTH-101	60.00	480.00	28,800.00
15	14	SAD2	CLOTH-104	10.00	1,790.00	17,900.00
16	15	SAD1	CLOTH-108	90.00	1,620.00	145,800.00
OPENING

Cell Formulas
Range	Formula
F2:F16	F2	=D2*E2

ABDD1.xlsm
	A	B	C	D	E	F	G
1	ITEM	CUSTOMER	DATE	ID	QTY	PRICE	TOTAL
2	1	ABBD1	15/01/2024	CLOTH-100	22.00	500.00	11,000.00
3	2	ABBD1	15/01/2024	CLOTH-101	25.00	525.00	13,125.00
4	3	SAD1	15/01/2024	CLOTH-102	60.00	920.00	55,200.00
5	4	SAD2	18/01/2024	CLOTH-103	50.00	1,000.00	50,000.00
6	5	SAD3	18/01/2024	CLOTH-104	110.00	1,800.00	198,000.00
7	6	SAD4	18/01/2024	CLOTH-105	140.00	1,800.00	252,000.00
8	7	ABBD1	21/01/2024	CLOTH-101	50.00	1,800.00	90,000.00
9	8	ABBD1	22/01/2024	CLOTH-107	60.00	450.00	27,000.00
10	9	SAD1	23/01/2024	CLOTH-108	88.00	1,650.00	145,200.00
11	10	SAD2	23/01/2024	CLOTH-109	90.00	1,660.00	149,400.00
12	11	SERW-00	23/01/2024	CLOTH-110	200.00	1,550.00	310,000.00
13	12	SERW-01	23/01/2024	CLOTH-101	120.00	420.00	50,400.00
14	13	SERW-00	23/01/2024	CLOTH-101	120.00	430.00	51,600.00
15	14	SAD2	28/01/2024	CLOTH-109	10.00	1,670.00	16,700.00
16	15	SAD1	29/01/2024	CLOTH-108	100.00	1,650.00	165,000.00
17	16	SERW-07	30/01/2024	CLOTH-112	120.00	1,640.00	196,800.00
18	17	SERW-08	31/01/2024	CLOTH-113	100.00	1,625.00	162,500.00
19	18	SERW-08	31/01/2024	CLOTH-113	20.00	1,625.00	32,500.00
BU

Cell Formulas
Range	Formula
G2:G19	G2	=E2*F2

ABDD1.xlsm
	A	B	C	D	E	F	G
1	ITEM	CUSTOMER	DATE	ID	QTY	PRICE	TOTAL
2	1	SERW-00	15/01/2024	CLOTH-110	10.00	1,600.00	16,000.00
3	2	SERW-01	15/01/2024	CLOTH-101	20.00	500.00	10,000.00
4	3	SERW-00	15/01/2024	CLOTH-101	25.00	480.00	12,000.00
5	4	SERW-00	15/01/2024	CLOTH-110	200.00	1,550.00	310,000.00
6	5	SAD3	18/01/2024	CLOTH-104	10.00	1,890.00	18,900.00
7	6	SAD3	19/01/2024	CLOTH-104	10.00	1,880.00	18,800.00
8	7	ABBD1	20/01/2024	CLOTH-106	5.00	1,900.00	9,500.00
9	8	ABBD1	21/01/2024	CLOTH-107	5.00	500.00	2,500.00
10	9	SAD4	21/01/2024	CLOTH-105	50.00	1,995.00	99,750.00
11	10	SAD3	21/01/2024	CLOTH-104	5.00	1,880.00	9,400.00
12	11	ABBD1	22/01/2024	CLOTH-106	10.00	1,990.00	19,900.00
13	12	ABBD1	22/01/2024	CLOTH-101	5.00	1,890.00	9,450.00
14	13	SAD3	22/01/2024	CLOTH-104	10.00	1,860.00	18,600.00
SE

Cell Formulas
Range	Formula
G2:G14	G2	=E2*F2

if the B3,C3 don't contain dates then the report in summary sheet will be

ABDD1.xlsm
	A	B	C	D	E	F	G	H	I
1
2		FROM DATE	TO DATE
3
4
5
6	ITEM	CUSTOMER	ID	OPENING	BU	SE	OPENING TOTAL	BU TOTAL	SE TOTAL
7	1	ABBD1	CLOTH-100	30.00	22.00		14,400.00	11,000.00
8	2	ABBD1	CLOTH-101	60.00	75.00	5.00	28,800.00	103,125.00	9,450.00
9	3	ABBD1	CLOTH-106			15.00			29,400.00
10	4	ABBD1	CLOTH-107		60.00	5.00		27,000.00	2,500.00
11	5	SAD1	CLOTH-101	50.00			24,500.00
12	6	SAD1	CLOTH-108	90.00	188.00		145,800.00	310,200.00
13	7	SAD1	CLOTH-102		60.00			55,200.00
14	8	SAD2	CLOTH-102	40.00			39,600.00
15	9	SAD2	CLOTH-103		50.00			50,000.00
16	10	SAD2	CLOTH-104	10.00			17,900.00
17	11	SAD2	CLOTH-109		100.00			166,100.00
18	12	SAD3	CLOTH-103	60.00			66,000.00
19	13	SAD3	CLOTH-104		110.00	35.00		198,000.00	65,700.00
20	14	SAD4	CLOTH-104	80.00			141,600.00
21	15	SAD4	CLOTH-105		140.00	50.00		252,000.00	99,750.00
22	16	SERW-00	CLOTH-101		120.00	25.00		51,600.00	12,000.00
23	17	SERW-00	CLOTH-105	100.00			168,000.00
24	18	SERW-00	CLOTH-110		200.00	210.00		310,000.00	326,000.00
25	19	SERW-01	CLOTH-101		120.00	20.00		50,400.00	10,000.00
26	20	SERW-01	CLOTH-106	120.00			202,800.00
27	21	SERW-02	CLOTH-107	140.00			81,200.00
28	22	SERW-03	CLOTH-108	160.00			265,600.00
29	23	SERW-04	CLOTH-109	180.00			306,000.00
30	24	SERW-05	CLOTH-110	200.00			320,000.00
31	25	SERW-06	CLOTH-111	201.00			357,780.00
32	26	SERW-07	CLOTH-112		120.00			196,800.00
33	27	SERW-08	CLOTH-113		120.00			195,000.00
Summary

will be brings all of customers , ID across sheets as in column B:C in summary sheet
will brings QTY and TOTAL from OPENING sheet as in column D,G
will merge duplicates based on two columns (CUSTOMER,ID) so will merge QTY,TOTAL columns for BU sheet and put in columns E,H in summary sheet
will merge duplicates based on two columns (CUSTOMER,ID) so will merge QTY,TOTAL columns for SE sheet and put in columns F,I in summary sheet


if the B3,C3 contain dates then will match with column C for BU,SE sheets the report in summary sheet will be

ABDD1.xlsm
	A	B	C	D	E	F	G	H	I
1
2		FROM DATE	TO DATE
3		15/01/2024	21/01/2024
4
5
6	ITEM	CUSTOMER	ID	OPENING	BU	SE	OPENING TOTAL	BU TOTAL	SE TOTAL
7	1	ABBD1	CLOTH-100	30.00	22.00		14,400.00	11,000.00
8	2	ABBD1	CLOTH-101	60.00	75.00		28,800.00	103,125.00
9	3	ABBD1	CLOTH-106			5.00			9,500.00
10	4	ABBD1	CLOTH-107			5.00			2,500.00
11	5	SAD1	CLOTH-101	50.00			24,500.00
12	6	SAD1	CLOTH-108	90.00			145,800.00
13	7	SAD1	CLOTH-102		60.00
14	8	SAD2	CLOTH-102	40.00			39,600.00
15	9	SAD2	CLOTH-103		50.00			50,000.00
16	10	SAD2	CLOTH-104	10.00			17,900.00
17	11	SAD2	CLOTH-109
18	12	SAD3	CLOTH-103	60.00			66,000.00
19	13	SAD3	CLOTH-104		110.00	25.00		198,000.00	47,100.00
20	14	SAD4	CLOTH-104	80.00			141,600.00
21	15	SAD4	CLOTH-105		140.00	50.00		252,000.00	99,750.00
22	16	SERW-00	CLOTH-101			25.00			12,000.00
23	17	SERW-00	CLOTH-105	100.00			168,000.00
24	18	SERW-00	CLOTH-110			210.00			326,000.00
25	19	SERW-01	CLOTH-101			20.00			10,000.00
26	20	SERW-01	CLOTH-106	120.00			202,800.00
27	21	SERW-02	CLOTH-107	140.00			81,200.00
28	22	SERW-03	CLOTH-108	160.00			265,600.00
29	23	SERW-04	CLOTH-109	180.00			306,000.00
30	24	SERW-05	CLOTH-110	200.00			320,000.00
31	25	SERW-06	CLOTH-111	201.00			357,780.00
Summary

as you see will brings QTY,TOTAL from opening sheet even search by two dates.
I hope some body help me towrite macro to deal with big data for each sheet .

 

[DanteAmor]

Try:

Sub populate_summary()
  Dim sh1 As Worksheet
  Dim dic As Object, ky As Variant
  Dim a As Variant, b As Variant, c As Variant, d As Variant
  Dim i%, y%, nRow%
  Dim dt1 As Long, dt2 As Long, ini As Long, fin As Long
  
  Set sh1 = Sheets("Summary")
  Set dic = CreateObject("Scripting.Dictionary")
  dt1 = sh1.Range("B3").Value
  dt2 = sh1.Range("C3").Value
  
  a = Sheets("OPENING").Range("A2", Sheets("OPENING").Range("F" & Rows.Count).End(3)).Value2
  b = Sheets("BU").Range("A2", Sheets("BU").Range("G" & Rows.Count).End(3)).Value2
  c = Sheets("SE").Range("A2", Sheets("SE").Range("G" & Rows.Count).End(3)).Value2
  
  ReDim d(1 To UBound(a, 1) + UBound(b, 1) + UBound(c, 1), 1 To 9)
  
  'OPENING
  For i = 1 To UBound(a, 1)
    ky = a(i, 2) & "|" & a(i, 3)
    If Not dic.exists(ky) Then dic(ky) = dic.Count + 1
    nRow = dic(ky)
    d(nRow, 2) = a(i, 2)
    d(nRow, 3) = a(i, 3)
    d(nRow, 4) = d(nRow, 4) + a(i, 4)
    d(nRow, 7) = d(nRow, 7) + a(i, 6)
  Next
  
  'BU
  For i = 1 To UBound(b, 1)
    If dt1 = 0 Then ini = b(i, 3) Else ini = dt1
    If dt2 = 0 Then fin = b(i, 3) Else fin = dt2
    If b(i, 3) >= ini And b(i, 3) <= fin Then
      ky = b(i, 2) & "|" & b(i, 4)
      If Not dic.exists(ky) Then dic(ky) = dic.Count + 1
      nRow = dic(ky)
      d(nRow, 2) = b(i, 2)
      d(nRow, 3) = b(i, 4)
      d(nRow, 5) = d(nRow, 5) + b(i, 5)
      d(nRow, 8) = d(nRow, 8) + b(i, 7)
    End If
  Next
  
  'SE
  For i = 1 To UBound(c, 1)
    If dt1 = 0 Then ini = c(i, 3) Else ini = dt1
    If dt2 = 0 Then fin = c(i, 3) Else fin = dt2
    If c(i, 3) >= ini And c(i, 3) <= fin Then
      ky = c(i, 2) & "|" & c(i, 4)
      If Not dic.exists(ky) Then dic(ky) = dic.Count + 1
      nRow = dic(ky)
      d(nRow, 2) = c(i, 2)
      d(nRow, 3) = c(i, 4)
      d(nRow, 6) = d(nRow, 6) + c(i, 5)
      d(nRow, 9) = d(nRow, 9) + c(i, 7)
    End If
  Next
  
  Application.ScreenUpdating = False
  With sh1.Range("A7:I" & Rows.Count)
    .ClearContents
    .HorizontalAlignment = xlCenter
  End With
  sh1.Range("D7:I" & Rows.Count).NumberFormat = "#,##0.00"
  With sh1.Range("A7").Resize(UBound(d, 1), UBound(d, 2))
    .Value = d
    .Sort sh1.Range("B6"), xlAscending, sh1.Range("C6"), Header:=xlYes
  End With
  With sh1.Range("A7")
    .Value = 1
    .AutoFill Destination:=sh1.Range("A7:A" & sh1.Range("B" & Rows.Count).End(3).Row), Type:=xlFillSeries
  End With
  
  Application.ScreenUpdating = True
End Sub

 

[Abdo]

wow !
this is really fast and great!
I've found some errors when brings the numbers for some ID in my OP when I tested your code , sorry about it
many thanks for the code.

 

[DanteAmor]

I've found some errors when brings the numbers for some ID in my OP when I tested your code , sorry about it

Yes I noticed that. But your examples are quite good, I even take them as a model for other users to present their examples like yours.