Extraction same sheet name from files in folder and merge the same price

[abdo meghari]

Hello,
I would macro, not POWER QUERY to extract the whole sheets from files are existed in directory D:\ab\INVOICES
so every file contains one sheet , the same sheet name .

INV1.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	TOTAL		39.00		28,485.00
SH1

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

INV2.xlsm
	B	C	D	E	F
21	ITEM	BRAND	QTY	PRICE	BALANCE
22	1	VEGA 70A R KOR	1.00	500.00	500.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	1.00	460.00	460.00
26	5	265/70R16 ALGERIA	4.00	630.00	2,520.00
27	6	KM 205/65R16 HS63 KOR	4.00	450.00	1,800.00
28	7	GC 385/65R22.5 AT131 CHI	12.00	1,640.00	19,680.00
29	8	KM 265/70R16 KOR	4.00	880.00	3,520.00
30	9	KM 235/65R17 HP71 KOR	1.00	875.00	875.00
31	10	KM 235/55R19 PS71 KOR	2.00	670.00	1,340.00
32	TOTAL		33.00		32,425.00
SH1

Cell Formulas
Range	Formula
D32,F32	D32	=SUM(D22:D31)
F22:F31	F22	=E22*D22

INV3.xlsm
	B	C	D	E	F
21	ITEM	BRAND	QTY	PRICE	BALANCE
22	1	BJS 13R22.5 MA11 KOR	10.00	2,555.00	25,550.00
23	2	KM 13R22.5 MA11 KOR	12.00	2,000.00	24,000.00
24	TOTAL		22.00		49,550.00
SH1

Cell Formulas
Range	Formula
D24,F24	D24	=SUM(D22:D23)
F22:F23	F22	=D22*E22

so the result in OUTPUT file into result sheet will already be existed and rename sheet for each file contains same sheet name based on file name INV1,INV2,INV3 before RESULT sheet. after data copy the data from sheets and merge QTY in column D for each brand IN COLUMN C if the price is the same thing in column E for duplicates brands whether in the same sheet or all of sheets across files otherwise don't merge , surely without forget insert TOTAL row to sum amounts.


before

OUTPUT.xlsm
	B	C	D	E	F
21	ITEM	BRAND	QTY	PRICE	BALANCE
22
23
24
25
26
27
28
RESULT

after

OUTPUT.xlsm
	B	C	D	E	F
21	ITEM	BRAND	QTY	PRICE	BALANCE
22	1	VEGA 70A R KOR	5	500.00	2,500.00
23	2	VEGA 55A R KOR	4	425.00	1,700.00
24	3	LARGEST 60A L HIGH JAP	4	440.00	1,760.00
25	4	KM 215/65R16 TA31 KOR	5	460.00	2,300.00
26	5	265/70R16 ALGERIA	8	630.00	5,040.00
27	6	KM 205/65R16 HS63 KOR	4	445.00	1,780.00
28	7	GC 385/65R22.5 AT131 CHI	2	1,650.00	3,300.00
29	8	KM 265/70R16 KOR	8	880.00	7,040.00
30	9	DONGA 66A L KOR	2	460.00	920.00
31	10	KM 235/65R17 HP71 KOR	4	625.00	2,500.00
32	11	KM 235/55R19 PS71 KOR	4	675.00	2,700.00
33	12	DROUB 90A L KOR	1	575.00	575.00
34	13	KM 13R22.5 MA11 KOR	2	2,550.00	5,100.00
35	14	KM 205/65R16 HS63 KOR	4	450.00	1,800.00
36	15	GC 385/65R22.5 AT131 CHI	12	1,640.00	19,680.00
37	16	KM 235/65R17 HP71 KOR	1	875.00	875.00
38	17	KM 235/55R19 PS71 KOR	2	670.00	1,340.00
39	18	BJS 13R22.5 MA11 KOR	10	2,555.00	25,550.00
40	19	KM 13R22.5 MA11 KOR	12	2,000.00	24,000.00
41	TOTAL		94		110,460.00
RESULT

Cell Formulas
Range	Formula
D41,F41	D41	=SUM(D22:D40)

every time when run macro should delete sheets before RESULT sheet also delete data in RESULT sheet before brings data.
thanks

 

[abdo meghari]

this is what I have.

by the way works with 2019 version , but 2010 not !

 

[mumps]

I happen to have Excel 2010 and when I tested the macro using 2010, it worked properly for me. this is the result:

OUTPUT2.xlsm
	B	C	D	E	F
21	ITEM	BRAND	QTY	PRICE	BALANCE
22	1	VEGA 70A R KOR	6	500.00	3,000.00
23	2	VEGA 55A R KOR	4	425.00	1,700.00
24	3	LARGEST 60A L HIGH JAP	4	440.00	1,760.00
25	4	KM 215/65R16 TA31 KOR	7	460.00	3,220.00
26	5	265/70R16 ALGERIA	8	630.00	5,040.00
27	6	KM 205/65R16 HS63 KOR	4	445.00	1,780.00
28	7	GC 385/65R22.5 AT131 CHI	2	1,650.00	3,300.00
29	8	KM 265/70R16 KOR	8	880.00	7,040.00
30	9	DONGA 66A L KOR	2	460.00	920.00
31	10	KM 235/65R17 HP71 KOR	4	625.00	2,500.00
32	11	KM 235/55R19 PS71 KOR	4	675.00	2,700.00
33	12	DROUB 90A L KOR	1	575.00	575.00
34	13	KM 13R22.5 MA11 KOR	2	2,550.00	5,100.00
35	14	KM 215/65R16 TA31 KOR	2	450.00	900.00
36	15	KM 205/65R16 HS63 KOR	4	450.00	1,800.00
37	16	GC 385/65R22.5 AT131 CHI	12	1,640.00	19,680.00
38	17	KM 235/65R17 HP71 KOR	1	875.00	875.00
39	18	KM 235/55R19 PS71 KOR	2	670.00	1,340.00
40	19	BJS 13R22.5 MA11 KOR	10	2,555.00	25,550.00
41	20	KM 13R22.5 MA11 KOR	36	2,000.00	72,000.00
42	21	BJS 14R22.5 MA11 KOR	10	2,500.00	25,000.00
43	22	KM 14R22.5 MA11 KOR	12	2,500.00	30,000.00
44	TOTAL		145		215,780.00
RESULT

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

I don't know why your version of 2010 is not working.

 

[abdo meghari]

Thanks for confirmation that .
as long works with version 2019 is not problem .
thanks for your help.

 

[mumps]

You are very welcome.