Extract Brands contains different prices with comparison original prices

[abdo meghari]

Hi,
I search for macro to create report in range I:N for two sheets PURCHASE and SALES
I will take PURCHASE sheet as example:

MG.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	DATE	CUSTOMER	INVOICE NO	BRAND	QTY	PRICE	TOTAL		DATE	BRAND	QTY	PRICE	DIFFERNCE(+/-)	BALANCE		ITEM	BRAND	PRICE
2	01/01/2024	Abdom1	BSJ1000	BS 750R16 R230 JAP	22.00	460.00	10,120.00									1	BS 700R16 R230	400.00
3	01/01/2024	Abdom1	BSJ1000	BS 700R16 R230	10.00	410.00	4,100.00									2	BS 750R16 R230 JAP	450.00
4	TOTAL						14,220.00									3	BS 205/70R15C R623	500.00
5	01/01/2024	Abdom1	BSJ1001	BS 700R16 R230	10.00	390.00	3,900.00									4	BS 1200R20 G580 JAP	2,800.00
6	TOTAL						3,900.00									5	BS 1200R20 G580 THI	2,500.00
7	02/01/2024	Abbdo m	BSJ1002	BS 1200R20 G580 JAP	20.00	2,800.00	56,000.00									6	BS 1200R20 R187 THI	3,000.00
8	02/01/2024	Abbdo m	BSJ1002	BS 750R16 R230 JAP	10.00	440.00	4,400.00									7	DT 1200R20 050A THI	3,200.00
9	02/01/2024	Abbdo m	BSJ1002	BS 205/70R15C R623	10.00	500.00	5,000.00
10	TOTAL						65,400.00
PURCHASE

Cell Formulas
Range	Formula
G7:G9,G5,G2:G3	G2	=E2*F2
G4	G4	=SUM(G2:G3)
G6	G6	=SUM(G5)
G10	G10	=SUM(G7:G9)

what I want extract columns(DATE,,BRAND,QTY,PRICE) from columns A,D,E,F to columns I:L after that the result in column M will subtract prices column L from column R and column N = column M * column K and insert SUM row to sum column M,N and should highlight font minus value for columns M,N .so the result will be like this

MG.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	DATE	CUSTOMER	INVOICE NO	BRAND	QTY	PRICE	TOTAL		DATE	BRAND	QTY	PRICE	DIFFERNCE(+/-)	BALANCE		ITEM	BRAND	PRICE
2	01/01/2024	Abdom1	BSJ1000	BS 750R16 R230 JAP	22.00	460.00	10,120.00		01/01/2024	BS 750R16 R230 JAP	22	460.00	10.00	220.00		1	BS 700R16 R230	400.00
3	01/01/2024	Abdom1	BSJ1000	BS 700R16 R230	10.00	410.00	4,100.00		01/01/2024	BS 700R16 R230	10	410.00	10.00	100.00		2	BS 750R16 R230 JAP	450.00
4	TOTAL						14,220.00		01/01/2024	BS 700R16 R230	10	390.00	-10.00	-100.00		3	BS 205/70R15C R623	500.00
5	01/01/2024	Abdom1	BSJ1001	BS 700R16 R230	10.00	390.00	3,900.00		02/01/2024	BS 1200R20 G580 JAP	20	2,800.00	0.00	0.00		4	BS 1200R20 G580 JAP	2,800.00
6	TOTAL						3,900.00		02/01/2024	BS 750R16 R230 JAP	10	440.00	-10.00	-100.00		5	BS 1200R20 G580 THI	2,500.00
7	02/01/2024	Abbdo m	BSJ1002	BS 1200R20 G580 JAP	20.00	2,800.00	56,000.00		02/01/2024	BS 205/70R15C R623	10	500.00	0.00	0.00		6	BS 1200R20 R187 THI	3,000.00
8	02/01/2024	Abbdo m	BSJ1002	BS 750R16 R230 JAP	10.00	440.00	4,400.00		03/01/2024	BS 1200R20 G580 JAP	10	2,850.00	50.00	500.00		7	DT 1200R20 050A THI	3,200.00
9	02/01/2024	Abbdo m	BSJ1002	BS 205/70R15C R623	10.00	500.00	5,000.00		SUM				50.00	620.00
10	TOTAL						65,400.00
11	03/01/2024	Abbdo m	BSJ1003	BS 1200R20 G580 JAP	10.00	2,850.00	28,500.00
12	TOTAL						28,500.00
PURCHASE

Cell Formulas
Range	Formula
M2	M2	=L2-R3
N2:N8	N2	=M2*K2
M3	M3	=L3-R2
M4	M4	=L4-R2
M5	M5	=L5-R5
M6:M8	M6	=L6-R3
M9:N9	M9	=SUM(M2:M8)
G11,G7:G9,G5,G2:G3	G2	=E2*F2
G4	G4	=SUM(G2:G3)
G6,G12	G6	=SUM(G5)
G10	G10	=SUM(G7:G9)

the result for SALES sheet will be the same thing for the same structure .
the data in range A:G could be 9000 rows for both sheets
should delete data in range I: N from row2 before create report.
please don't give me solution by Power Query because doesn't work for me for many times .
thanks

 

[kvsrinivasamurthy]

Welcome