copy data from multiple sheets based on headers into another sheet

[abdelfattah]

hi all
I have many sheets contains data . what I would copy data based on headers in sheet result from specific sheets(SS,SSA,SSR) with considering there items are repeated in some sheets . it supposses merge and summing the values based on COL B
NOTE: I have about 5000 rows and increasable .
sheet SS

HEADERS.xlsx
	A	B	C	D	E
1	DATE	BATCH NO	BRAND	TYPE	PURCHASE
2	01/01/2021	BT-OL1-100	CL-MEN	SHIRT	100
3	02/01/2021	BT-OL1-100	CL-MEN	SHIRT	120
4	03/01/2021	BT-OL1-100	CL-MEN	SHIRT	20
5	04/01/2021	BT-OL1-101	CL-MEN	JEANS	120
6	05/01/2021	BT-OL1-101	CL-MEN	JEANS	10
7	06/01/2021	BT-OL1-102	CL-WOMEN	SKIRT	15
8	07/01/2021	BT-OL1-102	CL-WOMEN	SKIRT	20
9	08/01/2021	BT-OL1-102	CL-WOMEN	SKIRT	25
10	09/01/2021	BT-OL1-102	CL-WOMEN	SKIRT	20
11	10/01/2021	BT-OL1-103	CL-WOMEN	DRESS	21
SS

sheet SSA

HEADERS.xlsx
	A	B	C	D	E
1	DATE	BATCH NO	BRAND	TYPE	SALES
2	21/01/2021	BT-OL1-100	CL-MEN	SHIRT	5
3	22/01/2021	BT-OL1-100	CL-MEN	SHIRT	5
4	23/01/2021	BT-OL1-102	CL-WOMEN	SKIRT	2
SSA

sheet SSR

HEADERS.xlsx
	A	B	C	D	E
1	DATE	BATCH NO	BRAND	TYPE	RETURNS
2	25/01/2021	BT-OL1-100	CL-MEN	SHIRT	3
3	26/01/2021	BT-OL1-100	CL-MEN	SHIRT	1
SSR

before in sheet RESULT

HEADERS.xlsx
	A	B	C	D	E
1	ITEM	BATCH NO	PURCHASE	SALES	RETURNS
2
3
4
5
RESULT

AFTER

HEADERS.xlsx
	A	B	C	D	E
1	ITEM	BATCH NO	PURCHASE	SALES	RETURNS
2	1	BT-OL1-100	240	10	4
3	2	BT-OL1-101	130	-	-
4	3	BT-OL1-102	80	2	-
5	4	BT-OL1-103	21	-	-
RESULT

 

[alansidman]

With Power Query, Load each sheet to the PQ editor. Group each sheet on the Batch Nr., Join the sheets on a Left inner join (merge) the files. If you wish to see the file, then click on this link.

PQ Join Sheets.xlsx | Powered by Box

app.box.com

 

[abdelfattah]

thanks , but I search doing by vba

 

[alansidman]

Ok. Good Luck with that. I am out.