Match three columns together across sheet to subtraction based invoice number

[abdo meghari]

Hello
I have theses data for four sheets

DECREASE.xlsm
	A	B	C	D	E	F	G	H	I
1	ITEM	DATE	INV.NO	BRAND	TYPE	ORIGIN	QTY	PRICE	TOTAL
2	1	15/06/2023	BSTR_23448	BS 750R16	R230	JAP	4.00	500.00	2,000.00
3	2	15/06/2023	BSTR_23448	BS 700R16	R230	JAP	2.00	400.00	800.00
4	SUM								2,800.00
5	1	15/09/2023	BSTR_23449	GO 1200R20	AZ0026	CHI	1.00	920.00	920.00
6	2	15/09/2023	BSTR_23449	GO 1200R20	AZ0083	CHI	2.00	1,000.00	2,000.00
7	SUM								2,920.00
8	1	15/09/2023	BSTR_23450	BS 1200R20	G580	JAP	10.00	1,800.00	18,000.00
9	2	15/09/2023	BSTR_23450	BS 1200R20	G580	THI	10.00	1,800.00	18,000.00
10	3	15/09/2023	BSTR_23450	BS 1200R20	R187	THI	10.00	1,800.00	18,000.00
11	SUM								54,000.00
12	1	16/09/2023	BSTR_23451	BS 215/60R16	ER30	JAP	4.00	400.00	1,600.00
13	SUM								1,600.00
14	1	16/09/2023	BSTR_23452	BS 1200R20	G580	JAP	5.00	1,800.00	9,000.00
15	SUM								9,000.00
16	1	16/09/2023	BSTR_23453	BS 1200R20	G580	JAP	5.00	1,880.00	9,400.00
17	SUM								9,400.00
SV

DECREASE.xlsm
	A	B	C	D	E	F	G	H	I
1	ITEM	DATE	INV.NO	BRAND	TYPE	ORIGIN	QTY	PRICE	TOTAL
2	1	10/06/2023	BSJ_23444	BS 215/60R16	ER30	JAP	4.00	430.00	1,720.00
3	SUM								1,720.00
4	1	10/06/2023	BSJ_23445	GO 1200R20	AZ0026	CHI	2.00	955.00	1,910.00
5	SUM								1,910.00
6	1	15/09/2023	BSJ_23446	GO 1200R20	AZ0026	CHI	2.00	950.00	1,900.00
7	2	15/09/2023	BSJ_23446	GO 1200R21	AZ0027	CHI	3.00	1,000.00	3,000.00
8	SUM								4,900.00
9	1	15/09/2023	BSJ_23447	BS 1200R20	G580	JAP	1.00	2,000.00	2,000.00
10	2	15/09/2023	BSJ_23447	BS 1200R20	G580	THI	1.00	2,000.00	2,000.00
11	3	15/09/2023	BSJ_23447	BS 1200R20	R187	THI	1.00	2,000.00	2,000.00
12	SUM								6,000.00
SR

DECREASE.xlsm
	A	B	C	D	E	F	G	H	I	J
1	ITEM	DATE	INV.NO	BRAND	TYPE	ORIGIN	QTY	PRICE	TOTAL	NOTICE
2	1	15/06/2023	VSTR_23444	BS 750R16	R230	JAP	1.00	500.00	500.00	INV.NO BSTR_23448
3	SUM								500.00
4	1	15/09/2023	VSTR_23445	GO 1200R20	AZ0083	CHI	1.00	1,000.00	1,000.00	INV.NO BSTR_23449
5	SUM								1,000.00
6	1	15/09/2023	VSTR_23446	BS 1200R20	G580	JAP	1.00	1,800.00	1,800.00	INV NO BSTR_23450
7	2	15/09/2023	VSTR_23446	BS 1200R20	G580	THI	1.00	1,800.00	1,800.00	INV NO BSTR_23450
8	SUM								3,600.00
9	1	16/09/2023	VSTR_23447	BS 215/60R16	ER30	JAP	4.00	400.00	1,600.00	INV NO BSTR_23451
10	SUM								1,600.00
11	1	16/09/2023	VSTR_23448	BS 1200R20	G580	JAP	1.00	1,800.00	1,800.00	INV NO BSTR_23452
12	SUM								1,800.00
13	1	16/09/2023	VSTR_23449	BS 1200R20	G580	JAP	2.00	1,880.00	3,760.00	INV NO BSTR_23453
14	SUM								3,760.00
VS

DECREASE.xlsm
	A	B	C	D	E	F	G	H	I	J
2	1	10/06/2023	RSS_23222	BS 215/60R16	ER30	JAP	2.00	430.00	860.00	INV.NO BSJ_23444
3	SUM								860.00
4	1	10/06/2023	BSJ_23445	GO 1200R20	AZ0026	CHI	1.00	955.00	955.00	INV.NO BSJ_23445
5	SUM								955.00
6	1	15/09/2023	BSJ_23446	GO 1200R20	AZ0026	CHI	1.00	950.00	950.00	INV.NO BSJ_23446
7	2	15/09/2023	BSJ_23446	GO 1200R21	AZ0027	CHI	2.00	1,000.00	2,000.00	INV.NO BSJ_23446
8	SUM								2,950.00
RS

what I want matching columns D:F in SV sheet with columns D:F in VS sheet based on invoice number is in column(J)
if the invoice number in column J in s VS sheet is the same invoice number in column(C) in SV sheet then should subtract QTY for ID in columns D:F in SV sheet from VS sheet and change calculation for TOTAL column for each ID and SUM row for whole invoice number and should put word DONE in column J for adjacant ID cells to avoid subtraction every time repeatedly(meaning when there is DONE word shouldn't do any thing). and if there is the same ID for the same QTY then should delete the whole row for ID like 215/60R16 ER30 and whole row for SUM row if there is no another ID for the same invoice number .as to PRICE column will not change because will be the same

so in result in SV sheet should be as highlighted cells

DECREASE.xlsm
	A	B	C	D	E	F	G	H	I	J
1	ITEM	DATE	INV.NO	BRAND	TYPE	ORIGIN	QTY	PRICE	TOTAL
2	1	15/06/2023	BSTR_23448	BS 750R16	R230	JAP	3.00	500.00	1,500.00	DONE
3	2	15/06/2023	BSTR_23448	BS 700R16	R230	JAP	2.00	400.00	800.00	DONE
4	SUM								2,300.00
5	1	15/09/2023	BSTR_23449	GO 1200R20	AZ0026	CHI	1.00	920.00	920.00
6	2	15/09/2023	BSTR_23449	GO 1200R20	AZ0083	CHI	1.00	1,000.00	1,000.00	DONE
7	SUM								1,920.00
8	1	15/09/2023	BSTR_23450	BS 1200R20	G580	JAP	9.00	1,800.00	16,200.00	DONE
9	2	15/09/2023	BSTR_23450	BS 1200R20	G580	THI	9.00	1,800.00	16,200.00	DONE
10	3	15/09/2023	BSTR_23450	BS 1200R20	R187	THI	10.00	1,800.00	18,000.00
11	SUM								50,400.00
12	1	16/09/2023	BSTR_23452	BS 1200R20	G580	JAP	4.00	1,800.00	7,200.00	DONE
13	SUM								7,200.00
14	1	16/09/2023	BSTR_23453	BS 1200R20	G580	JAP	3.00	1,880.00	5,640.00	DONE
15	SUM								5,640.00
SV

also result in SR sheet bases on RS sheet with the same way as I did it in SV sheet.
expected data in SV,SR sheets contain 8500 rows .
thanks

 

[abdo meghari]

Great !
many thanks buddy.