Merge QTY & AMOUNT for each part of item for each range

[Amer Omar]

Hello
I have theses data in sheets .

AMR.xlsm
	A	B	C	D	E	F	G
1	DATE	BATCH	INVOICE NO	CLIENT NO	QTY	PRICE	TOTAL
2	11/01/2024	AVDF FG-900 L200	INFLOW CVBF AAA100	BVCDG5000	200.00	220.00	44,000.00
3	12/01/2024	AVDF FG-900 L201	INFLOW CVBF AAA101	BVCDG5001	100.00	220.00	22,000.00
4	13/01/2024	AVDF FG-900 L202	OUTFLOW CVBF BBB100	BVCDG5002	50.00	120.00	6,000.00
5	14/01/2024	AVDF FG-900 L203	INFLOW CVBF AAA102	BVCDG5003	60.00	110.00	6,600.00
6	15/01/2024	AVDF FG-900 L204	OUTFLOW CVBF BBB101	BVCDG5004	70.00	115.00	8,050.00
7	16/01/2024	AVDF FG-900 L205	INFLOW CVBF AAA103	BVCDG5005	80.00	120.00	9,600.00
8	17/01/2024	AVDF FG-900 L206	GO BACK INFLOW GGG5000	BVCDG5006	90.00	125.00	11,250.00
9	18/01/2024	AVDF FG-900 L200	GO BACK INFLOW GGG5001	BVCDG5007	100.00	130.00	13,000.00
10	19/01/2024	AVDF FG-900 L201	GO BACK INFLOW GGG5002	BVCDG5008	110.00	135.00	14,850.00
11	20/01/2024	AVDF FG-900 L202	GO BACK OUTFLOW FFF6000	BVCDG5009	120.00	140.00	16,800.00
12	21/01/2024	AVDF FG-900 L207	GO BACK OUTFLOW FFF6001	BVCDG5010	130.00	145.00	18,850.00
13	22/01/2024	AVDF FG-900 L207	UNDER INFLOW CVFG9000	BVCDG5010	20.00	145.00	2,900.00
14	23/01/2024	AVDF FG-900 L204	UNDER OUTFLOW CVBF UNN7800	BVCDG5004	10.00	115.00	1,150.00
15	24/01/2024	AVDF FG-900 L200	UNDER INFLOW CVFG9001	BVCDG5000	100.00	220.00	22,000.00
16	25/01/2024	AVDF FG-900 L202	UNDER OUTFLOW CVBF UNN7801	BVCDG5002	10.00	120.00	1,200.00
CVD

Cell Formulas
Range	Formula
G2:G16	G2	=E2*F2

AMR.xlsm
	A	B	C	D	E	F	G
1	DATE	BATCH	INVOICE NO	CLIENT NO	QTY	PRICE	TOTAL
2	11/01/2024	AVDF FG-900 L200	OUTFLOW CVBF BBB101	BVCDG5000	10.00	244.00	2,440.00
3	12/01/2024	AVDF FG-900 L201	OUTFLOW CVBF BBB102	BVCDG5000	10.00	126.00	1,260.00
4	13/01/2024	AVDF FG-900 L202	OUTFLOW CVBF BBB103	BVCDG5000	5.00	150.00	750.00
5	14/01/2024	AVDF FG-900 L203	INFLOW CVBF AAA104	BVCDG5001	20.00	140.00	2,800.00
6	15/01/2024	AVDF FG-900 L200	INFLOW CVBF AAA105	BVCDG5002	10.00	155.00	1,550.00
7	16/01/2024	AVDF FG-900 L201	INFLOW CVBF AAA103	BVCDG5003	80.00	144.00	11,520.00
8	17/01/2024	AVDF FG-900 L202	GO BACK INFLOW GGG5003	BVCDG5004	90.00	130.00	11,700.00
9	18/01/2024	AVDF FG-900 L203	GO BACK OUTFLOW FFF6002	BVCDG5005	100.00	150.00	15,000.00
10	19/01/2024	AVDF FG-900 L204	GO BACK INFLOW GGG5004	BVCDG5006	110.00	140.00	15,400.00
11	20/01/2024	AVDF FG-900 L205	GO BACK OUTFLOW FFF6003	BVCDG5007	120.00	200.00	24,000.00
12	21/01/2024	AVDF FG-900 L206	GO BACK OUTFLOW FFF6004	BVCDG5008	10.00	155.00	1,550.00
13	22/01/2024	AVDF FG-900 L202	UNDER OUTFLOW CVBF UNN7802	BVCDG5009	50.00	220.00	11,000.00
14	23/01/2024	AVDF FG-900 L203	UNDER INFLOW CVFG9003	BVCDG5010	60.00	230.00	13,800.00
15	24/01/2024	AVDF FG-900 L204	UNDER OUTFLOW CVBF UNN7803	BVCDG5011	70.00	260.00	18,200.00
16	25/01/2024	AVDF FG-900 L205	UNDER INFLOW CVFG9004	BVCDG5012	100.00	225.00	22,500.00
17	26/01/2024	AVDF FG-900 L206	UNDER INFLOW CVFG9005	BVCDG5013	300.00	250.00	75,000.00
CVRM

Cell Formulas
Range	Formula
G2:G17	G2	=E2*F2

AMR.xlsm
	A	B	C	D	E	F	G	H	I
1							FROM DATE		TO DATE
2
3
4		CVD
5	ITEM	INVOICE NO	QTY	TOTAL
6	1	INFLOW
7	2	GO BACK INFLOW
8	3	UNDER INFLOW
9	BALANCE			0.00
10
11
12		CVRM
13	ITEM	INVOICE NO	QTY	TOTAL
14	1	OUTFLOW
15	2	GO BACK OUTFLOW
16	3	UNDER OUTFLOW
17	BALANCE			0.00
OUTPUT

Cell Formulas
Range	Formula
D9,D17	D9	=D6-D7-D8

result
if G2,I2 are empty then match part of item in column B for OUTPUT sheet with item in column C for others sheets then should merge QTY,AMOUNT E,G

AMR.xlsm
	A	B	C	D	E	F	G	H	I
1							FROM DATE		TO DATE
2
3
4		CVD
5	ITEM	INVOICE NO	QTY	TOTAL
6	1	INFLOW	550.00	98,070.00
7	2	GO BACK INFLOW	500.00	66,200.00
8	3	UNDER INFLOW	580.00	136,200.00
9	BALANCE			-104,330.00
10
11
12		CVRM
13	ITEM	INVOICE NO	QTY	TOTAL
14	1	OUTFLOW	145.00	18,500.00
15	2	GO BACK OUTFLOW	480.00	76,200.00
16	3	UNDER OUTFLOW	140.00	31,550.00
17	BALANCE			-89,250.00
OUTPUT

Cell Formulas
Range	Formula
D9,D17	D9	=D6-D7-D8

if there are two dates in G2,I2(DD/MM/YYYY) then should merge within dates .
data could reach for 10000 rows for each sheet.
doing that by formula or vba is acceptable , otherwise I don't prefer like PQ or PT.
thanks

 

[DanteAmor]

doing that by formula

How about:

Dante Amor
	A	B	C	D	E	F	G	H	I	J
1							FROM DATE		TO DATE
2
3
4		CVD
5	ITEM	INVOICE NO	QTY	TOTAL
6	1	INFLOW	550.00	98,070.00
7	2	GO BACK INFLOW	500.00	66,200.00
8	3	UNDER INFLOW	580.00	136,200.00
9	BALANCE			-104,330.00
10
11
12		CVRM
13	ITEM	INVOICE NO	QTY	TOTAL
14	1	OUTFLOW	145.00	18,500.00
15	2	GO BACK OUTFLOW	480.00	76,200.00
16	3	UNDER OUTFLOW	140.00	31,550.00
17	BALANCE			-89,250.00
18
19
OUTPUT

Cell Formulas
Range	Formula
C14:C16,C6:C8	C6	=SUMIFS(CVD!E:E,CVD!C:C,B6&"*",CVD!A:A,">="&MAX(G$2,1),CVD!A:A,"<="&MIN(I$2,9^9)) +SUMIFS(CVRM!E:E,CVRM!C:C,B6&"*",CVRM!A:A,">="&MAX(G$2,1),CVRM!A:A,"<="&MIN(I$2,9^9))
D14:D16,D6:D8	D6	=SUMIFS(CVD!G:G,CVD!C:C,B6&"*",CVD!A:A,">="&MAX(G$2,1),CVD!A:A,"<="&MIN(I$2,9^9)) +SUMIFS(CVRM!G:G,CVRM!C:C,B6&"*",CVRM!A:A,">="&MAX(G$2,1),CVRM!A:A,"<="&MIN(I$2,9^9))
D9,D17	D9	=D6-D7-D8

 

[Amer Omar]

Perfect !
thank you so much.