compare extraction right data for information invoices with wrong data for each range

[abdo meghari]

Hi
I want compare data based on column B CODE between two sheets.
first the result should be from row2 in COMPARE sheet just copy data without formatting I will do that manually.
second in SH1 will match CODE with DATA sheet and will copy CODE,BRAND,INVOICE NO from SH1 sheet to column B,C,D in COMPARE sheet (sometimes will I have more than one CODE in SH1 so when copy the INVOICE NO then should repeat in column D for COMPARE sheet as I did it ) , also will brings PRICE,TOTAL (G,H) for each item from SH1 to columns F,H in COMPARE sheet
third brings INVOICE NO(D)(sometimes will I have more than one CODE in DATA so when copy the INVOICE NO then should repeat in column E for COMPARE sheet as I did it ), PRICE,TOTAL (J,K) from DATA sheet to column E,G,I in COMPARE sheet
finally in columns J,K will calculate as I put some formulas if show minus values then will highlight red color
note: when brings values based on CODE should put for adjacent cells between two sheets to show arranging
every time when bring data should clear in COMPARE sheet from row2

ABDO.xlsm
	A	B	C	D	E	F	G	H
1				INVOICE NO
2				A7179
3	ITEM	CODE	BRAND	TYPE	ORIGIN	QTY	PRICE	TOTAL
4	1	BSJ100	Dayton 1200r24 DA53 THI	Dayton	THILAND	4.00	1,650.00	6,600.00
5							TOTAL	6,600.00
6							DISCOUNT
7							NET	6,600.00
8
9				INVOICE NO
10				A7180
11	ITEM	CODE	BRAND	TYPE	ORIGIN	QTY	PRICE	TOTAL
12	1	BSJ100	Dayton 1200r24 DA53 THI	Dayton	THILAND	4.00	1,650.00	6,600.00
13	2	BSJ101	BS 1200R20 G580 THI	BRIDGESTO NE	THILAND	5.00	2,000.00	10,000.00
14							TOTAL	16,600.00
15							DISCOUNT
16							NET	16,600.00
17
18				INVOICE NO
19				A7185
20	ITEM	CODE	BRAND	TYPE	ORIGIN	QTY	PRICE	TOTAL
21	1	BSJ101	BS 1200R20 G580 THI	BRIDGESTO NE	THILAND	4.00	2,000.00	8,000.00
22	2	BSJ102	BS 1200R20 G580 JAP	BRIDGESTO NE	JAPAN	5.00	1,900.00	9,500.00
23							TOTAL	17,500.00
24							DISCOUNT
25							NET	17,500.00
26
27				INVOICE NO
28				A7188
29	ITEM	CODE	BRAND	TYPE	ORIGIN	QTY	PRICE	TOTAL
30	1	BSJ101	BS 1200R20 G580 THI	BRIDGESTO NE	THILAND	4.00	2,000.00	8,000.00
31	2	BSJ103	BS 1200R20 R187 JAP	BRIDGESTO NE	JAPAN	2.00	2,110.00	4,220.00
32	3	BSJ102	BS 1200R20 G580 JAP	BRIDGESTO NE	JAPAN	5.00	1,900.00	9,500.00
33							TOTAL	21,720.00
34							DISCOUNT
35							NET	21,720.00
SH1

Cell Formulas
Range	Formula
H4,H30:H32,H21:H22,H12:H13	H4	=F4*G4
H5	H5	=SUM(H4:H4)
H7,H35,H25,H16	H7	=H5-H6
H14,H23	H14	=SUM(H12:H13)
H33	H33	=SUM(H30:H32)

ABDO.xlsm
	C	D	E	F	G	H	I	J	K
1	DATE	INVOIC NO	CUSTOMER	BRAND	TYPE	ORIGIN	QTY	UNIT PRICE	TOTAL
2	31/05/2023	PTR-10000	CRST	Dayton 1200r24	DA53	THI	4.00	1,660.00	6,640.00
3	31/05/2023	PTR-10000	CRST						6,640.00
4	31/05/2023	PTR-10001	VFDG	Dayton 1200r24	DA53	THI	4.00	1,640.00	6,560.00
5	31/05/2023	PTR-10001	VFDG	BS 1200R20	G580	THI	5.00	2,000.00	10,000.00
6	31/05/2023	PTR-10001	VFDG						16,560.00
7	31/05/2023	PTR-10002	VFDG	BS 1200R20	G580	THI	4.00	2,000.00	8,000.00
8	31/05/2023	PTR-10002	VFDG	BS 1200R20	G580	JAP	5.00	1,900.00	9,500.00
9	31/05/2023	PTR-10002	VFDG						17,500.00
10	31/05/2023	PTR-10003	VFDG	BS 1200R20	G580	THI	4.00	1,980.00	7,920.00
11	31/05/2023	PTR-10003	MNHTY	BS 1200R20	R187	JAP	2.00	2,110.00	4,220.00
12	31/05/2023	PTR-10003	MNHTY	BS 1200R20	G580	JAP	5.00	1,910.00	9,550.00
13	31/05/2023	PTR-10003	MNHTY						21,690.00
DATA

Cell Formulas
Range	Formula
K4:K5	K4	=J4*I4

ABDO.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	ITEM	CODE	BRAND	RIGHT INVOCE NO	WRONG INVOCE NO	RIGHT PRICE	WRONG PRICE	RIGHT TOTAL	WRONG TOTAL	DIFFERENCE PRICE	DIFFERENCE TOTAL
2
3
4
5
6
7
8
9
COMPARE

result what I want it

ABDO.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	ITEM	CODE	BRAND	RIGHT INVOCE NO	WRONG INVOCE NO	RIGHT PRICE	WRONG PRICE	RIGHT TOTAL	WRONG TOTAL	DIFFERENCE PRICE	DIFFERENCE TOTAL
2	1	BSJ100	Dayton 1200r24 DA53 THI	A7179	PTR-10000	1,650.00	1,660.00	6,600.00	6,640.00	-10.00	-40.00
3	2	BSJ100	Dayton 1200r24 DA53 THI	A7180	PTR-10001	1,650.00	1,640.00	6,600.00	6,560.00	10.00	40.00
4	3	BSJ101	BS 1200R20 G580 THI	A7180	PTR-10001	2,000.00	2,000.00	10,000.00	10,000.00	0.00	0.00
5	4	BSJ101	BS 1200R20 G580 THI	A7185	PTR-10002	2,000.00	2,000.00	8,000.00	8,000.00	0.00	0.00
6	5	BSJ102	BS 1200R20 G580 JAP	A7185	PTR-10002	1,900.00	1,900.00	9,500.00	9,500.00	0.00	0.00
7	6	BSJ101	BS 1200R20 G580 THI	A7188	PTR-10003	2,000.00	1,980.00	8,000.00	7,920.00	20.00	80.00
8	7	BSJ103	BS 1200R20 R187 JAP	A7188	PTR-10003	2,110.00	2,110.00	4,220.00	4,220.00	0.00	0.00
9	8	BSJ102	BS 1200R20 G580 JAP	A7188	PTR-10003	1,900.00	1,910.00	9,500.00	9,550.00	-10.00	-50.00
COMPARE

Cell Formulas
Range	Formula
J2:J9	J2	=F2-G2
K2:K9	K2	=H2-I2

 

[abdo meghari]

sorry experts !
I realized there are missed columns about second picture
should be

ABDO
	A	B	C	D	E	F	G	H	I	J	K
1	ITEM	CODE	DATE	INVOIC NO	CUSTOMER	BRAND	TYPE	ORIGIN	QTY	UNIT PRICE	TOTAL
2	1	BSJ100	31/05/2023	PTR-10000	CRST	Dayton 1200r24	DA53	THI	4.00	1,660.00	6,640.00
3	TOTAL		31/05/2023	PTR-10000	CRST						6,640.00
4	1	BSJ100	31/05/2023	PTR-10001	VFDG	Dayton 1200r24	DA53	THI	4.00	1,640.00	6,560.00
5	2	BSJ101	31/05/2023	PTR-10001	VFDG	BS 1200R20	G580	THI	5.00	2,000.00	10,000.00
6	TOTAL		31/05/2023	PTR-10001	VFDG						16,560.00
7	1	BSJ101	31/05/2023	PTR-10002	VFDG	BS 1200R20	G580	THI	4.00	2,000.00	8,000.00
8	2	BSJ102	31/05/2023	PTR-10002	VFDG	BS 1200R20	G580	JAP	5.00	1,900.00	9,500.00
9	TOTAL		31/05/2023	PTR-10002	VFDG						17,500.00
10	1	BSJ101	31/05/2023	PTR-10003	VFDG	BS 1200R20	G580	THI	4.00	1,980.00	7,920.00
11	2	BSJ103	31/05/2023	PTR-10003	MNHTY	BS 1200R20	R187	JAP	2.00	2,110.00	4,220.00
12	3	BSJ102	31/05/2023	PTR-10003	MNHTY	BS 1200R20	G580	JAP	5.00	1,910.00	9,550.00
13	TOTAL		31/05/2023	PTR-10003	MNHTY						21,690.00
DATA

Cell Formulas
Range	Formula
K4:K5	K4	=J4*I4

 

[abdo meghari]

I hope finding help .