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 .

 

[Peter_SSs]

You're welcome.

 

[abdo meghari]

Hi peter again
I try implement for this data , but suddenly merge error as highlighted , what's the reason?!

KashfMabiatReport1 with code.xls
	A	B	C	D	E	F	G
3	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
4	2024.07.20	1135	1568	MOTORMECH 150A L KOR	2	1,000.000	9,995.000
5			1586	NOVA 200A L KOR	2	1,500.000
6			1540	VEGA 55A L KOR	1	400.000
7			1300	XPRO 70A L KOR	4	540.000
8			1334	XPRO 70A R KOR	4	540.000
9			1588	BATTREY 30A CHI	1	275.000
10	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
11	2024.07.22	1141	1590	BOHANO 55A L JAP	1	370.000	370.000
12	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
13	2024.07.23	1146	1343	KM 215/65R16 TA21 KOR	5	435.000	3,575.000
14			1518	KM 195/65R15 TA21 KOR	4	350.000
15	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
16	2024.07.27	970000002	1335	XPRO 150A L KOR	2	1,000.000	6,115.000
17			1496	XPRO 100A L KOR واطية	1	625.000
18			1448	DUNLOP 285/60R18 AT25 JAP	1	1,110.000
19			1594	APPLLO 265/65R17 INDIA	4	595.000
20	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
21	2024.07.28	970000011	1471	BS 1400R20 R180 JAP	2	4,500.000	9,000.000
22	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
23	2024.07.29	970000017	1495	XPRO 200A L KOR	4	1,285.000	44,780.000
24			1335	XPRO 150A L KOR	4	1,000.000
25			1595	XPRO 100A R KOR	10	610.000
26			1341	XPRO 100A L KOR	10	620.000
27			1334	XPRO 70A R KOR	15	510.000
28			1300	XPRO 70A L KOR	10	515.000
29			1338	XPRO 60A L KOR	15	425.000
30			1337	XPRO 55A L KOR	10	390.000
31			1596	YOKOHAMA 7A MALAYSIA	1	265.000
32	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
33	2024.08.01	970000026	1598	XPRO 80A L KPOR	1	570.000	570.000
34	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
35	2024.08.27	970000216	1620	YOKOHAMA 205/65R16 ES32 JAP	4	455.000	15,160.000
36			1624	DUNLOP 265/70R18 AT23 JAP	4	1,100.000
37			1621	LASSA 4*4 235/70R16 A/T2 TR	4	575.000
38			1623	LASSA 235/50R19 H/P2 TR	4	560.000
39			1567	DUNLOP 285/65R17 JAP	2	950.000
40			1619	VEGA 70A R KOR	5	500.000
41	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
42	2024.08.29	970000022	1339	XPRO 90A L KOR	9	605.000	5,445.000
43	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
44	2024.08.31	970000227	1598	XPRO 80A L KPOR	1	570.000	570.000
45	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
46	2024.08.31	970000233	1626	KM 285/75R16 MT51 VIT	8	835.000	6,680.000
47	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
48	2024.09.02	970000247	1338	XPRO 60A L KOR	2	425.000	850.000
49	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
50	2024.09.04	970000272	1631	KM 245/70R17 AT52 VIT	4	645.000	2,580.000
51	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
52	2024.08.27	970000216	1620	YOKOHAMA 205/65R16 ES32 JAP	4	455.000	15,160.000
53			1624	DUNLOP 265/70R18 AT23 JAP	4	1,100.000
54			1621	LASSA 4*4 235/70R16 A/T2 TR	4	575.000
55			1623	LASSA 235/50R19 H/P2 TR	4	560.000
56			1567	DUNLOP 285/65R17 JAP	2	950.000
57			1619	VEGA 70A R KOR	5	500.000
58	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
59	2024.08.29	970000022	1339	XPRO 90A L KOR	9	605.000	5,445.000
60	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
61	2024.08.31	970000227	1598	XPRO 80A L KPOR	1	570.000	570.000
62	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
63	2024.08.31	970000233	1626	KM 285/75R16 MT51 VIT	8	835.000	6,680.000
64	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
65	2024.09.02	970000247	1338	XPRO 60A L KOR	2	425.000	850.000
66	DATE	INVOICE NO	CODE	BRAND	QTY	UNIT PRICE	TOTAL
67	2024.09.04	970000272	1631	KM 245/70R17 AT52 VIT	4	645.000	2,580.000
INVOICES

this is what I got

KashfMabiatReport1 with code.xls
	A	B	C	D	E	F
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
RESULT

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

seem adding numbers without sum for some brands !

 

[alansidman]

PQ editor will inform me the Mcode was written in a newer version.

I have never seen this before. You are on 2019 and I am on 365. My code should be compatible with your version. Is your source the same as in my Mcode.