Populate data under header and combined based on the same price

[abdo meghari]

in SH1 sheet I would match headers with headers in others sheets and populate data under header and combine duplicates Brand if contains the same price
without forgetting add new row before TOTAL row if I have data much in others sheets or delete empty row before TOTAL row if the data are small in other sheets with comparison empty rows in SH1 sheet.I will add new sheets before SH1 sheet.

فاتورة 58720.xlsm
	A	B	C	D	E	F	G
1	TOTAL	DATE	INVOICE NO	PRICE	QTY	CODE	BRAND
2	7,000.000	2024.10.12	970000444	500.000	14	1657	DROUB 70A L KOR
3	TOTAL	DATE	INVOICE NO	PRICE	QTY	CODE	BRAND
4	2,065.000	2024.10.17	970000466	385.000	4	1518	KM 195/65R15 TA21 KOR
5				525.000	1	1672	VIPIEMME 70A L IT
6	TOTAL	DATE	INVOICE NO	PRICE	QTY	CODE	BRAND
7	8,525.000	2024.10.19	970000471	500.000	10	1657	DROUB 70A L KOR
8				500.000	6	1673	DROUB 70A R KOR
9				525.000	1	1672	VIPIEMME 70A L IT
10	TOTAL	DATE	INVOICE NO	PRICE	QTY	CODE	BRAND
11	1,860.000	2024.10.28	970000496	465.000	4	1386	KM 225/60R16 TA21 KOR
12	TOTAL	DATE	INVOICE NO	PRICE	QTY	CODE	BRAND
13	5,815.000	2024.10.29	970000499	775.000	5	1677	KM 285/60R18 KL61 VIT
14				420.000	2	1676	DROUB 60A L KOR
15				550.000	2	1339	XPRO 90A L KOR
16	TOTAL	DATE	INVOICE NO	PRICE	QTY	CODE	BRAND
17	475.000	2024.10.30	970000502	475.000	1	1334	XPRO 70A R KOR
Page 0

فاتورة 58720.xlsm
	A	B	C	D	E	F	G
1	TOTAL	DATE	INVOICE NO	PRICE	QTY	CODE	BRAND
2	3,730.000	2024.09.15	970000315	500.000	2	1619	VEGA 70A R KOR
3				500.000	2	1541	VEGA 70A L KOR
4				425.000	2	1641	VEGA 55A R KOR
5				440.000	2	1642	LARGEST 60A L HIGH JAP
6	TOTAL	DATE	INVOICE NO	PRICE	QTY	CODE	BRAND
7	4,360.000	2024.09.16	970000329	460.000	4	1643	KM 215/65R16 TA31 KOR
8				630.000	4	1645	265/70R16 ALGERIA
9	TOTAL	DATE	INVOICE NO	PRICE	QTY	CODE	BRAND
10	1,780.000	2024.09.16	970000334	445.000	4	1646	KM 205/65R16 HS63 KOR
11	TOTAL	DATE	INVOICE NO	PRICE	QTY	CODE	BRAND
12	3,300.000	2024.09.21	970000345	1,650.000	2	1492	GC 385/65R22.5 AT131 CHI
13	TOTAL	DATE	INVOICE NO	PRICE	QTY	CODE	BRAND
14	3,520.000	2024.09.24	970000360	880.000	4	1651	KM 265/70R16 KOR
15	TOTAL	DATE	INVOICE NO	PRICE	QTY	CODE	BRAND
16	920.000	2024.10.02	970000392	460.000	2	1656	DONGA 66A L KOR
17	TOTAL	DATE	INVOICE NO	PRICE	QTY	CODE	BRAND
18	10,875.000	2024.10.09	970000427	625.000	4	1390	KM 235/65R17 HP71 KOR
19				675.000	4	1662	KM 235/55R19 PS71 KOR
20				575.000	1	1661	DROUB 90A L KOR
21				2,550.000	2	1663	KM 13R22.5 MA11 KOR
22	TOTAL	DATE	INVOICE NO	PRICE	QTY	CODE	BRAND
23	2,120.000	2024.10.09	970000430	530.000	3	1619	VEGA 70A R KOR
24				530.000	1	1457	BATTERY 70A KOR
25	TOTAL	DATE	INVOICE NO	PRICE	QTY	CODE	BRAND
26	2,140.000	2024.10.10	970000435	535.000	4	1665	KM 225/70R16 TA21 KOR
Page 0 (2)

فاتورة 58720.xlsm
	B	C	D	E	F
21	ITEM	BRAND	QTY	PRICE	BALANCE
22					0.00
23					0.00
24					0.00
25					0.00
26					0.00
27					0.00
28					0.00
29					0.00
30					0.00
31					0.00
32					0.00
33					0.00
34					0.00
35	TOTAL		0.00		0.00
SH1

Cell Formulas
Range	Formula
D35,F35	D35	=SUM(D22:D34)
F22:F34	F22	=D22*E22

result I want

فاتورة 58720.xlsm
	B	C	D	E	F
21	ITEM	BRAND	QTY	PRICE	BALANCE
22	1	VEGA 70A R KOR	4.00	500.00	2,000.00
23	2	VEGA 55A R KOR	2.00	425.00	850.00
24	3	LARGEST 60A L HIGH JAP	2.00	440.00	880.00
25	4	KM 215/65R16 TA31 KOR	4.00	460.00	1,840.00
26	5	265/70R16 ALGERIA	4.00	630.00	2,520.00
27	6	KM 205/65R16 HS63 KOR	4.00	445.00	1,780.00
28	7	GC 385/65R22.5 AT131 CHI	2.00	1,650.00	3,300.00
29	8	KM 265/70R16 KOR	4.00	880.00	3,520.00
30	9	DONGA 66A L KOR	2.00	460.00	920.00
31	10	KM 235/65R17 HP71 KOR	4.00	625.00	2,500.00
32	11	KM 235/55R19 PS71 KOR	4.00	675.00	2,700.00
33	12	DROUB 90A L KOR	1.00	575.00	575.00
34	13	KM 13R22.5 MA11 KOR	2.00	2,550.00	5,100.00
35	14	VEGA 70A R KOR	4.00	530.00	2,120.00
36	15	KM 225/70R16 TA21 KOR	4.00	535.00	2,140.00
37	16	KM 195/65R15 TA21 KOR	4.00	385.00	1,540.00
38	17	DROUB 70A L KOR	30.00	500.00	15,000.00
39	18	VIPIEMME 70A L It	2.00	525.00	1,050.00
40	19	KM 225/60R16 TA21 KOR	4.00	465.00	1,860.00
41	20	KM 285/60R18 KL61 VIT	5.00	775.00	3,875.00
42	21	DROUB 60A L KOR	2.00	420.00	840.00
43	22	XPRO 90A L KOR	2.00	550.00	1,100.00
44	23	XPRO 70A R KOR	1.00	475.00	475.00
45	TOTAL		97.00		58,485.00
SH1

Cell Formulas
Range	Formula
D45,F45	D45	=SUM(D22:D44)
F22:F44	F22	=D22*E22

thanks

 

[DanteAmor]

combine duplicates Brand if contains the same price

Check your result, you are not combining the Brand correctly and if the price is duplicated.

You also omitted this brand: BATTERY 70A KOR

And I don't understand this part:

without forgetting add new row before TOTAL row if I have data much in others sheets or delete empty row before TOTAL row if the data are small in other sheets with comparison empty rows in SH1 sheet

Try:

Sub Populate_data()
  Dim sh As Worksheet, sh1 As Worksheet
  Dim idx As Long, lr As Long, i As Long, y As Long, nRow As Long
  Dim dic As Object
  Dim a As Variant, b As Variant
  Dim tQty As Double, tBal As Double
  Dim ky As String
  
  Set sh1 = Sheets("SH1")
  Set dic = CreateObject("Scripting.Dictionary")

  For idx = 1 To Sheets.Count
    Set sh = Sheets(idx)
    If sh.Name <> sh1.Name Then
      lr = lr + sh.Range("G" & Rows.Count).End(3).Row
    End If
  Next
  ReDim b(1 To lr, 1 To 5)
  
  For idx = 1 To Sheets.Count
    Set sh = Sheets(idx)
    If sh.Name <> sh1.Name Then
      a = sh.Range("A2", sh.Range("G" & Rows.Count).End(3)).Value
      
      For i = 1 To UBound(a, 1)
        If a(i, 1) <> "TOTAL" Then
          ky = a(i, 7) & "|" & a(i, 4)
          If Not dic.exists(ky) Then
            y = y + 1
            dic(ky) = y
          End If
          nRow = dic(ky)
          b(nRow, 1) = nRow
          b(nRow, 2) = a(i, 7)
          b(nRow, 3) = b(nRow, 3) + a(i, 5)
          b(nRow, 4) = a(i, 4)
          b(nRow, 5) = b(nRow, 3) * b(nRow, 4)
          tQty = tQty + a(i, 5)
          tBal = tBal + (a(i, 4) * a(i, 5))
        End If
      Next
      
    End If
  Next
  
  Application.ScreenUpdating = False
  
  sh1.Range("B22:F" & Rows.Count).Clear
  sh1.Range("B22").Resize(UBound(b, 1), UBound(b, 2)).Value = b
  lr = sh1.Range("C" & Rows.Count).End(3).Row
  With sh1.Range("B" & lr + 1)
    .Value = "TOTAL"
    .Font.Bold = True
    .Offset(0, 2).Value = tQty
    .Offset(0, 4).Value = tBal
  End With
  With sh1.Range("D22:F" & lr + 1)
    .NumberFormat = "#,##0.00"
  End With
  With sh1.Range("B22:F" & lr + 1)
    .HorizontalAlignment = xlCenter
    .Borders.LineStyle = xlContinuous
  End With
  
  Application.ScreenUpdating = True
End Sub

Result:

Dante Amor
	B	C	D	E	F
21	ITEM	BRAND	QTY	PRICE	BALANCE
22	1	DROUB 70A L KOR	24.00	500.00	12,000.00
23	2	KM 195/65R15 TA21 KOR	4.00	385.00	1,540.00
24	3	VIPIEMME 70A L IT	2.00	525.00	1,050.00
25	4	DROUB 70A R KOR	6.00	500.00	3,000.00
26	5	KM 225/60R16 TA21 KOR	4.00	465.00	1,860.00
27	6	KM 285/60R18 KL61 VIT	5.00	775.00	3,875.00
28	7	DROUB 60A L KOR	2.00	420.00	840.00
29	8	XPRO 90A L KOR	2.00	550.00	1,100.00
30	9	XPRO 70A R KOR	1.00	475.00	475.00
31	10	VEGA 70A R KOR	2.00	500.00	1,000.00
32	11	VEGA 70A L KOR	2.00	500.00	1,000.00
33	12	VEGA 55A R KOR	2.00	425.00	850.00
34	13	LARGEST 60A L HIGH JAP	2.00	440.00	880.00
35	14	KM 215/65R16 TA31 KOR	4.00	460.00	1,840.00
36	15	265/70R16 ALGERIA	4.00	630.00	2,520.00
37	16	KM 205/65R16 HS63 KOR	4.00	445.00	1,780.00
38	17	GC 385/65R22.5 AT131 CHI	2.00	1,650.00	3,300.00
39	18	KM 265/70R16 KOR	4.00	880.00	3,520.00
40	19	DONGA 66A L KOR	2.00	460.00	920.00
41	20	KM 235/65R17 HP71 KOR	4.00	625.00	2,500.00
42	21	KM 235/55R19 PS71 KOR	4.00	675.00	2,700.00
43	22	DROUB 90A L KOR	1.00	575.00	575.00
44	23	KM 13R22.5 MA11 KOR	2.00	2,550.00	5,100.00
45	24	VEGA 70A R KOR	3.00	530.00	1,590.00
46	25	BATTERY 70A KOR	1.00	530.00	530.00
47	26	KM 225/70R16 TA21 KOR	4.00	535.00	2,140.00
48	TOTAL		97.00		58,485.00
SH1

 

[abdo meghari]

Check your result, you are not combining the Brand correctly and if the price is duplicated.

You also omitted this brand: BATTERY 70A KOR

sorry about it
I tested but show mismatch error in this line

b(nRow, 5) = b(nRow, 3) * b(nRow, 4)

what does it mean ?

 

[DanteAmor]

sorry about it
I tested but show mismatch error in this line

what does it mean ?

It means you have letters where you should have numbers
Review the data on your sheets to verify that you do not have letters or cells with blank spaces in the Price and Qty columns.

To know the sheet and the row where you have the problem, run the macro, when the error occurs press the debug button, already in the code, bring the mouse pointer closer to the sh variable, a litte window will be shown there with the name of the sheet.

Now bring the mouse closer to the variable i, you will see a number, you increment that number by one and that is the row number in the sheet where you have the problem.

Check what you have in cells D and E of that row, take a screenshot if necessary and come back here and show it to me to see what problem you have.

 

[abdo meghari]

thanks for guiding me .
now I've found my bad . the problem was the header is not matched for some rows.
now every thing is ok
many thanks for your solution.