Merging duplicates items after words contains colon based on different price for the same ID

[abdo meghari]

Hello,
I would merge quantity in column E for duplicates BRANDS in column D based on column C for each range contains colon for each name MOVEMENT word is existed after colon .
, but when merge QTY should be based on the same price in unit price in column F . so you will note some the same duplicates IDs contain different price , so don't merge QTY for the same duplicates IDs contain different price .
when merge data should brings column C:F from INVOICES sheet and insert TOTAL column and calculate as the formula is existed .
every time when run the macro should replace new data with old data, also should sort data for each range based on column C from small to big based on numbers . .
last thing I have many ranges contains words and suffix :
I realized error about my topic . should merge same ID based on same price, not different price.
I can't correct my topic, sorry about this mistake!!

split brands.xlsm
	A	B	C	D	E	F	G
2				MOVEMENT : PURCHASING
3	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
4	2024.01.02	114	1284	GC 1200R20 AZ0026 CHI	80	895.000	242,560.000
5			1285	GC 1200R20 AZ0183 CHI	40	925.000
6			1385	GC 315/80R22.5 AT161 CHI	20	735.000
7			1287	GC 315/80R22.5 AZ126 CHI	20	735.000
8			1294	GC 315/80R22.5 AZ188 CHI	20	745.000
9			1241	BS 1200R20 G580 JAP	40	2,035.000
10			1227	BS 215/70R15C R623 THI	8	425.000
11			1221	BS 205/70R15C R623 THI	12	405.000
12	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
13	2024.01.09	121	1287	GC 315/80R22.5 AZ126 CHI	14	735.000	10,290.000
14	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
15	2024.01.10	125	1227	BS 215/70R15C R623 THI	2	425.000	850.000
16	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
17	2024.01.20	148	1287	GC 315/80R22.5 AZ126 CHI	26	735.000	19,110.000
18	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
19	2024.01.20	149	1221	BS 205/70R15C R623 THI	50	405.000	391,650.000
20			1227	BS 215/70R15C R623 THI	12	425.000
21			1241	BS 1200R20 G580 JAP	180	2,035.000
22	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
23	2024.03.30	217	1285	GC 1200R20 AZ0183 CHI	10	1,225.000	12,250.000
24	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
25	2024.04.09	226	1227	BS 215/70R15C R623 THI	4	544.000	2,176.000
26	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
27	2024.04.14	230	1285	GC 1200R20 AZ0183 CHI	40	1,205.000	81,950.000
28			1284	GC 1200R20 AZ0026 CHI	30	1,125.000
29	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
30	2024.04.17	236	1284	GC 1200R20 AZ0026 CHI	20	1,125.000	46,600.000
31			1285	GC 1200R20 AZ0183 CHI	20	1,205.000
32				MOVEMENT : SELLING
33	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
34	2024.01.24	561	1306	BS 750R16 R230 JAP	10	775.000	7,750.000
35	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
36	2024.01.24	562	1306	BS 750R16 R230 JAP	2	780.000	1,560.000
37	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
38	2024.01.24	563	1306	BS 750R16 R230 JAP	2	780.000	3,100.000
39			1305	BS 700R16 R230 JAP	2	770.000
40
41	SUMMARY
42	TOTAL PURCHASE	807,436.000
43	TOTAL SELLING	12,410.000
INVOICES

result

split brands.xlsm
	A	B	C	D	E	F
2			MOVEMENT : PURCHASING
3	ITEM	CODE	BRAND	QTY	UNIT PRICE	TOTAL
4	1	1221	BS 205/70R15C R623 THI	62	405.000	25,110.000
5	2	1227	BS 215/70R15C R623 THI	22	425.000	9,350.000
6	3	1227	BS 215/70R15C R623 THI	4	544.000	2,176.000
7	4	1241	BS 1200R20 G580 JAP	220	2,035.000	447,700.000
8	5	1284	GC 1200R20 AZ0026 CHI	80	895.000	71,600.000
9	6	1284	GC 1200R20 AZ0026 CHI	50	1,125.000	56,250.000
10	7	1285	GC 1200R20 AZ0183 CHI	40	925.000	37,000.000
11	8	1285	GC 1200R20 AZ0183 CHI	10	1,225.000	12,250.000
12	9	1285	GC 1200R20 AZ0183 CHI	60	1,205.000	72,300.000
13	10	1385	GC 315/80R22.5 AT161 CHI	20	735.000	14,700.000
14	11	1294	GC 315/80R22.5 AZ188 CHI	20	745.000	14,900.000
15	12	1287	GC 315/80R22.5 AZ126 CHI	60	735.000	44,100.000
16			MOVEMENT : SELLING
17	ITEM	CODE	BRAND	QTY	UNIT PRICE	TOTAL
18	1	1305	BS 700R16 R230 JAP	2	770.000	1,540.000
19	2	1306	BS 750R16 R230 JAP	10	775.000	7,750.000
20	3	1306	BS 750R16 R230 JAP	4	780.000	3,120.000
21
22	SUMMARY
23	TOTAL PURCHASE	807,436.000
24	TOTAL SELLING	12,410.000
RESULT

Cell Formulas
Range	Formula
F18:F20,F4:F15	F4	=D4*E4

I hope to don't make any mistake .

 

[abdo meghari]

when I run the code this is what I got.

ERROR MERGING.xlsm
	A	B	C	D	E	F
1
2			MOVEMENT : PURCHASING
3	ITEM	CODE	BRAND	QTY	UNIT PRICE	TOTAL
4	1	1300	XPRO 70A L KOR	4	540.000	2,160.000
5	2	1300	XPRO 70A L KOR	10	515.000	5,150.000
6	3	1334	XPRO 70A R KOR	4	540.000	2,160.000
7	4	1334	XPRO 70A R KOR	15	510.000	7,650.000
8	5	1335	XPRO 150A L KOR	6	1,000.000	6,000.000
9	6	1337	XPRO 55A L KOR	10	390.000	3,900.000
10	7	1338	XPRO 60A L KOR	19	425.000	8,075.000
11	8	1339	XPRO 90A L KOR	18	605.000	10,890.000
12	9	1341	XPRO 100A L KOR	10	620.000	6,200.000
13	10	1343	KM 215/65R16 TA21 KOR	5	435.000	2,175.000
14	11	1448	DUNLOP 285/60R18 AT25 JAP	1	1,110.000	1,110.000
15	12	1471	BS 1400R20 R180 JAP	2	4,500.000	9,000.000
16	13	1495	XPRO 200A L KOR	4	1,285.000	5,140.000
17	14	1496	XPRO 100A L KOR واطية	1	625.000	625.000
18	15	1518	KM 195/65R15 TA21 KOR	4	350.000	1,400.000
19	16	1540	VEGA 55A L KOR	1	400.000	400.000
20	17	1567	DUNLOP 285/65R17 JAP	4	950.000	3,800.000
21	18	1568	MOTORMECH 150A L KOR	2	1,000.000	2,000.000
22	19	1586	NOVA 200A L KOR	2	1,500.000	3,000.000
23	20	1588	BATTREY 30A CHI	1	275.000	275.000
24	21	1590	BOHANO 55A L JAP	1	370.000	370.000
25	22	1594	APPLLO 265/65R17 INDIA	4	595.000	2,380.000
26	23	1595	XPRO 100A R KOR	10	610.000	6,100.000
27	24	1596	YOKOHAMA 7A MALAYSIA	1	265.000	265.000
28	25	1598	XPRO 80A L KPOR	3	570.000	1,710.000
29	26	1619	VEGA 70A R KOR	10	500.000	5,000.000
30	27	1620	YOKOHAMA 205/65R16 ES32 JAP	8	455.000	3,640.000
31	28	1621	LASSA 4*4 235/70R16 A/T2 TR	8	575.000	4,600.000
32	29	1623	LASSA 235/50R19 H/P2 TR	8	560.000	4,480.000
33	30	1624	DUNLOP 265/70R18 AT23 JAP	8	1,100.000	8,800.000
34	31	1626	KM 285/75R16 MT51 VIT	16	835.000	13,360.000
35	32	1631	KM 245/70R17 AT52 VIT	8	645.000	5,160.000
36
37	SUMMARY
38	TOTAL PURCHASE	170,305.000
39	CURRENT BALANCE	-438,240.000
RESULT

Cell Formulas
Range	Formula
F4:F35	F4	=D4*E4

and what I want

ERROR MERGING.xlsm
	A	B	C	D	E	F
1
2			MOVEMENT : PURCHASING
3	ITEM	CODE	BRAND	QTY	UNIT PRICE	TOTAL
4	1	1300	XPRO 70A L KOR	4	540.000	2,160.000
5	2	1300	XPRO 70A L KOR	10	515.000	5,150.000
6	3	1334	XPRO 70A R KOR	4	540.000	2,160.000
7	4	1334	XPRO 70A R KOR	15	510.000	7,650.000
8	5	1335	XPRO 150A L KOR	24	1,000.000	24,000.000
9	6	1337	XPRO 55A L KOR	10	390.000	3,900.000
10	7	1338	XPRO 60A L KOR	1522	425.000	646,850.000
11	8	1339	XPRO 90A L KOR	99	605.000	59,895.000
12	9	1341	XPRO 100A L KOR	10	620.000	6,200.000
13	10	1343	KM 215/65R16 TA21 KOR	5	435.000	2,175.000
14	11	1448	DUNLOP 285/60R18 AT25 JAP	1	1,110.000	1,110.000
15	12	1471	BS 1400R20 R180 JAP	2	4,500.000	9,000.000
16	13	1495	XPRO 200A L KOR	4	1,285.000	5,140.000
17	14	1496	XPRO 100A L KOR واطية	1	625.000	625.000
18	15	1518	KM 195/65R15 TA21 KOR	4	350.000	1,400.000
19	16	1540	VEGA 55A L KOR	1	400.000	400.000
20	17	1567	DUNLOP 285/65R17 JAP	22	950.000	20,900.000
21	18	1568	MOTORMECH 150A L KOR	2	1,000.000	2,000.000
22	19	1586	NOVA 200A L KOR	2	1,500.000	3,000.000
23	20	1588	BATTREY 30A CHI	1	275.000	275.000
24	21	1590	BOHANO 55A L JAP	1	370.000	370.000
25	22	1594	APPLLO 265/65R17 INDIA	4	595.000	2,380.000
26	23	1595	XPRO 100A R KOR	10	610.000	6,100.000
27	24	1596	YOKOHAMA 7A MALAYSIA	1	265.000	265.000
28	25	1598	XPRO 80A L KPOR	111	570.000	63,270.000
29	26	1619	VEGA 70A R KOR	55	500.000	27,500.000
30	27	1620	YOKOHAMA 205/65R16 ES32 JAP	44	455.000	20,020.000
31	28	1621	LASSA 4*4 235/70R16 A/T2 TR	44	575.000	25,300.000
32	29	1623	LASSA 235/50R19 H/P2 TR	44	560.000	24,640.000
33	30	1624	DUNLOP 265/70R18 AT23 JAP	44	1,100.000	48,400.000
34	31	1626	KM 285/75R16 MT51 VIT	88	835.000	73,480.000
35	32	1631	KM 245/70R17 AT52 VIT	44	645.000	28,380.000
36
37	SUMMARY
38	TOTAL PURCHASE	170,305.000
39	CURRENT BALANCE	-438,240.000
RESULT

Cell Formulas
Range	Formula
F4:F35	F4	=D4*E4

 

[Peter_SSs]

Thanks for the samples. Try this version

Sub Get_Results_v5()
  Dim d As Object
  Dim a As Variant
  Dim wsI As Worksheet, wsR As Worksheet
  Dim fr As Long, lr As Long, vlr As Long, i As Long, nr As Long
  Dim s As String
  
  Application.ScreenUpdating = False
  Set wsI = Sheets("INVOICES")
  Set wsR = Sheets("RESULT")
  wsR.UsedRange.EntireColumn.Delete
  Set d = CreateObject("Scripting.Dictionary")
  nr = 2
  With wsI.Columns("D")
    fr = .Find(What:="MOVEMENT", LookAt:=xlPart).Row
    vlr = .Cells(.Rows.Count).End(xlUp).Row
    Do
      lr = .Find(What:="MOVEMENT", After:=.Cells(fr), LookAt:=xlPart).Row - 1
      If lr < fr Then lr = vlr
      a = wsI.Range("C" & fr & ":F" & lr).Value
      For i = 2 To UBound(a)
        If a(i, 1) <> "CODE" Then
          s = a(i, 1) & ";" & a(i, 2) & ";;" & a(i, 4)
          d(s) = d(s) + Val(a(i, 3))
        End If
      Next i
      wsI.Range("B" & fr).Resize(2, 6).Copy Destination:=wsR.Range("A" & nr)
      wsR.Range("A" & nr + 1).Value = "ITEM"
      With wsR.Range("B" & nr + 2).Resize(d.Count)
        .Value = Application.Transpose(d.Keys)
        .TextToColumns DataType:=xlDelimited, Semicolon:=True, Comma:=False, Space:=False, Other:=False
        .Offset(, 2).Value = Application.Transpose(d.Items)
        .Offset(, 4).FormulaR1C1 = "=RC[-2]*RC[-1]"
        .Resize(, 5).Sort Key1:=.Columns(1), Order1:=xlAscending, Header:=xlNo
        .Offset(, -1).Value = Evaluate("row(1:" & d.Count & ")")
        With .Cells(0, 0).Resize(d.Count + 1, 6)
          .BorderAround xlContinuous
          .Borders(xlInsideVertical).LineStyle = xlContinuous
          .Borders(xlInsideHorizontal).LineStyle = xlContinuous
        End With
        .Cells(-1, 2).BorderAround xlContinuous
      End With
      nr = nr + d.Count + 2
      fr = .Find(What:="MOVEMENT", After:=.Cells(fr), LookAt:=xlPart).Row
      d.RemoveAll
    Loop Until fr < lr
  End With
  wsI.Range("A" & Rows.Count).End(xlUp).CurrentRegion.Copy Destination:=wsR.Range("A" & nr + 1)
  With wsR.Range("A" & Rows.Count).End(xlUp).CurrentRegion
    .BorderAround xlContinuous
    .Borders(xlInsideVertical).LineStyle = xlContinuous
    .Borders(xlInsideHorizontal).LineStyle = xlContinuous
  End With
  wsR.Range("B" & nr + 3).Resize(2).NumberFormat = "#,##0.000"
  wsR.Columns("E:F").NumberFormat = "#,##0.000"
  With wsR.UsedRange
    .ColumnWidth = 255
    .Columns.AutoFit
    .Rows.AutoFit
  End With
  wsR.DrawingObjects.Delete
  Application.ScreenUpdating = True
End Sub

 

[abdo meghari]

Thanks , but will delete button is connected with macro !

 

[Peter_SSs]

but will delete button is connected with macro !

I thought that you did not want the button copied from the INVOICES sheet to the RESULT sheet

I want prevent copy button again when run the macro

 

[abdo meghari]

I thought that you did not want the button copied from the INVOICES sheet to the RESULT sheet

sorry about that !
but what I meant when running the macro I noticed the code will copy the button again next to first button
so will be come two buttons, not one button that should be.

 

[Peter_SSs]

Well, I am unsure about your buttons and what they do, or should do. In your sample file ...

• There is a button on RESULT that appears to run the "Get_Results_v3 code
• There is a button on INVOICES that does nothing for me.
  Why is it there?
  Is is needed?
  What does it do or what should it do?

 

[abdo meghari]

Why is it there?
Is is needed?

yes because there is another macro depends on others sheets before INVOICE sheet in my real project.

• What does it do or what should it do?

will make report in INVOICE sheet .

 

[Peter_SSs]

Try this

1. In INVOICES, right-click the button and choose 'Size & Properties'
2. Under Properties select "Don't move or size with cells"
3. In the _v5 code remove the following line near the end of the code wsR.DrawingObjects.Delete

 

[abdo meghari]

Try this

1. In INVOICES, right-click the button and choose 'Size & Properties'
2. Under Properties select "Don't move or size with cells"
3. In the _v5 code remove the following line near the end of the code wsR.DrawingObjects.Delete

that's works .
I appreciate for your back for me and solve all of my problems.
many thanks for your time , help.

 

[Peter_SSs]

Cheers. Glad we got there in the end.