create report for 40000 rows to calculation stock across sheet based on sheet name in cell value

[Amer Omar]

Hello
I have five sheets , each sheet contains data about 40000 rows .
original data for each sheet

stt.xlsm
	A	B	C	D	E	F
1	ITEM	BATCH	REF NO	QTY	UNIT PRICE	TOTAL
2	1	VBBHGY7-00	FGFFH/00	200.00	95.00	19,000.00
3	2	VBBHGY7-01	FGFFH/01	230.00	88.00	20,240.00
4	3	VBBHGY7-02	FGFFH/02	300.00	95.00	28,500.00
5	4	VBBHGY7-03	FGFFH/03	400.00	90.00	36,000.00
6	5	VBBHGY7-14	FGFFH/14	110.00	70.00	7,700.00
7	6	VBBHGY7-15	FGFFH/15	120.00	10.00	1,200.00
8	7	VBBHGY7-42	FGFFH/42	130.00	10.00	1,300.00
9	8	VBBHGY7-43	FGFFH/43	100.00	10.00	1,000.00
10	9	VBBHGY7-1319	FGFFH/1319	1,725.00	690.50	1,191,112.50
LAST

Cell Formulas
Range	Formula
F2:F10	F2	=D2*E2

stt.xlsm
	A	B	C	D	E	F	G	H
1	DATE	CLIENT	INVOICE REF	BATCH	REF NO	QTY	UNIT PRICE	TOTAL
2	12/01/2023	CL-001	IN A220	VBBHGY7-00	FGFFH/00	110.00	95.00	10,450.00
3	12/01/2023	CL-001	IN A220	VBBHGY7-01	FGFFH/01	115.00	99.00	11,385.00
4	12/01/2023	CL-001	IN A220	VBBHGY7-02	FGFFH/02	120.00	100.00	12,000.00
5	12/01/2023	CL-001	IN A220	VBBHGY7-03	FGFFH/03	125.00	110.00	13,750.00
6	12/01/2023	CL-002	IN A221	VBBHGY7-14	FGFFH/14	130.00	66.00	8,580.00
7	12/01/2023	CL-002	IN A221	VBBHGY7-15	FGFFH/15	135.00	15.00	2,025.00
8	12/01/2023	CL-002	IN A221	VBBHGY7-42	FGFFH/42	195.00	23.00	4,485.00
9	12/01/2023	CL-002	IN A221	VBBHGY7-43	FGFFH/43	200.00	24.00	4,800.00
10	12/01/2023	CL-1281	IN A1500	VBBHGY7-31760	FGFFH/31760	22.00	111.00	2,442.00
DAILY PURCHASE

Cell Formulas
Range	Formula
H2:H10	H2	=F2*G2

stt.xlsm
	A	B	C	D	E	F	G	H
1	DATE	CLIENT	INVOICE REF	BATCH	REF NO	QTY	UNIT PRICE	TOTAL
2	12/01/2023	CDD-00	IN PP200	VBBHGY7-42	FGFFH/42	10.00	45.00	450.00
3	12/01/2023	CDD-00	IN PP200	VBBHGY7-43	FGFFH/43	15.00	44.00	660.00
4	12/01/2023	CDD-00	IN PP200	VBBHGY7-00	FGFFH/00	5.00	100.00	500.00
5	12/01/2023	CDD-00	IN PP200	VBBHGY7-01	FGFFH/01	5.00	120.00	600.00
6	12/01/2023	CDD-00	IN PP200	VBBHGY7-42	FGFFH/42	2.00	66.00	132.00
7	12/01/2023	CDD-01	IN PP201	VBBHGY7-43	FGFFH/43	2.00	48.00	96.00
DAILY SALES

Cell Formulas
Range	Formula
H2:H7	H2	=F2*G2

stt.xlsm
	A	B	C	D	E	F	G	H
1	DATE	CLIENT	INVOICE REF	BATCH	REF NO	QTY	UNIT PRICE	TOTAL
2	12/01/2023	CL-001	IN A220	VBBHGY7-00	FGFFH/00	10.00	95.00	950.00
3	12/01/2023	CL-001	IN A220	VBBHGY7-01	FGFFH/01	15.00	99.00	1,485.00
4	12/01/2023	CL-001	IN A220	VBBHGY7-02	FGFFH/02	20.00	100.00	2,000.00
5	12/01/2023	CL-001	IN A220	VBBHGY7-03	FGFFH/03	25.00	110.00	2,750.00
6	12/01/2023	CL-002	IN A221	VBBHGY7-14	FGFFH/14	30.00	66.00	1,980.00
7	12/01/2023	CL-002	IN A221	VBBHGY7-15	FGFFH/15	35.00	15.00	525.00
8	12/01/2023	CL-002	IN A221	VBBHGY7-42	FGFFH/42	95.00	23.00	2,185.00
9	12/01/2023	CL-002	IN A221	VBBHGY7-43	FGFFH/43	100.00	24.00	2,400.00
DAILY PURCHASE RTURNS

Cell Formulas
Range	Formula
H2:H9	H2	=F2*G2

stt.xlsm
	A	B	C	D	E	F	G	H
1	DATE	CLIENT	INVOICE REF	BATCH	REF NO	QTY	UNIT PRICE	TOTAL
2	12/01/2023	CDD-00	IN PP200	VBBHGY7-42	FGFFH/42	2.00	45.00	90.00
3	12/01/2023	CDD-00	IN PP200	VBBHGY7-43	FGFFH/43	2.00	44.00	88.00
DAILY SALES RETURNS

Cell Formulas
Range	Formula
H2:H3	H2	=F2*G2

when I write sheet name in D3 for SUMMARY sheet then should merge QTY and populate price average as in column C,D in summary sheet based on column (BATCH) and insert TOTAL row to sum column C,E

before

stt.xlsm
	A	B	C	D	E
2				sheet name
3
4	ITEM	BATCH	QTY	UNIT PRICE	TOTAL
5
6
7
8
9
10
11
Summary

result after

stt.xlsm
	A	B	C	D	E
1
2				sheet name
3				DAILY PURCHASE
4	ITEM	BATCH	QTY	UNIT PRICE	TOTAL
5	1	VBBHGY7-00	280.00	87.00	24,360.00
6	2	VBBHGY7-01	135.00	98.00	13,230.00
7	3	VBBHGY7-02	145.00	105.00	15,225.00
8	4	VBBHGY7-03	135.00	95.00	12,825.00
9	5	VBBHGY7-14	130.00	66.00	8,580.00
10	6	VBBHGY7-15	135.00	15.00	2,025.00
11	TOTAL		960.00		76,245.00
Summary

Cell Formulas
Range	Formula
C11,E11	C11	=SUM(C5:C10)
E5:E10	E5	=C5*D5

but if the D3 is empty then will be totally different story
should be like this

stt.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2				sheet name
3
4	ITEM	BATCH	LAST	DAILY PURCHASE	DAILY SALES	DAILY PURCHASE RTURNS	DAILY SALES RETURNS	QUANTITY	UNIT PRICE	TOTAL
5	1	VBBHGY7-00	200.00	280.00	5.00	0.00	2.00	477.00	89.00	42,453.00
6	2	VBBHGY7-01	230.00	135.00	5.00	0.00	0.00	360.00	94.67	34,081.20
7	3	VBBHGY7-02	300.00	145.00	0.00	0.00	0.00	445.00	101.67	45,243.15
8	4	VBBHGY7-03	400.00	135.00	0.00	0.00	0.00	535.00	93.33	49,931.55
9	5	VBBHGY7-14	110.00	130.00	0.00	0.00	0.00	240.00	57.00	13,680.00
10	6	VBBHGY7-15	120.00	135.00	0.00	0.00	0.00	255.00	27.50	7,012.50
11	7	VBBHGY7-42	130.00	195.00	12.00	0.00	21.00	334.00	16.50	5,511.00
12	8	VBBHGY7-43	100.00	200.00	17.00	0.00	25.00	308.00	17.00	5,236.00
13	9	VBBHGY7-1319	1,725.00	0.00	0.00	0.00	0.00	1,725.00	690.50	1,191,112.50
14	10	VBBHGY7-31761	0.00	23.00	0.00	0.00	0.00	23.00	112.00	2,576.00
15	TOTAL							4,702.00		1,396,836.90
Summary

Cell Formulas
Range	Formula
H5:H14	H5	=C5+D5-E5-F5+G5
H15,J15	H15	=SUM(H5:H14)
J5:J14	J5	=H5*I5

will create headers in row 4 based on sheet names and under header sheet should merge QTY for each sheet based on column BATCH , as to column QUANTITY should calculate based on formula I put it , the UNIT PRICE column should populate price average based on two sheets (LAST,DAILY PURCHASE) together . also if there is new batch is existed in LAST sheet and is not existed in DAILY PURCHASE sheet should show in summary sheet when D3 is empty ,if there is new batch is existed in DAILY PURCHASE sheet and is not existed in sheet LAST should show in summary sheet when D3 is empty as highlighted cells by red
last thing every time run the macro should clear data before brings data based on D3 .
if anybody needs more details please tell me.
I hope who master write code to help me for this project .

 

[Amer Omar]

I hope the missed data in picture 5 for DAILY SALES RETURNS sheet this is not main reason to nobody answers me after two weeks ago .
here is the correct after add missed data

stt.xlsm
	A	B	C	D	E	F	G	H
1	DATE	CLIENT	INVOICE REF	BATCH	REF NO	QTY	UNIT PRICE	TOTAL
2	12/01/2023	CDD-00	IN PP200	VBBHGY7-42	FGFFH/42	2.00	45.00	90.00
3	12/01/2023	CDD-00	IN PP200	VBBHGY7-43	FGFFH/43	2.00	44.00	88.00
4	12/01/2023	CDD-00	IN PP200	VBBHGY7-42	FGFFH/42	8.00	40.00	320.00
5	12/01/2023	CDD-00	IN PP200	VBBHGY7-43	FGFFH/43	8.00	41.00	328.00
6	12/01/2023	CDD-03	IN PP203	VBBHGY7-42	FGFFH/42	11.00	39.00	429.00
7	12/01/2023	CDD-04	IN PP204	VBBHGY7-43	FGFFH/43	15.00	38.00	570.00
DAILY SALES RETURNS

Cell Formulas
Range	Formula
H2:H7	H2	=F2*G2