Replace formula with zero when populate Right mark for two sheets

[abdo meghari]

Hello

I need to populate right or wrong mark in column I and replace zero in column H for one of the two sheets based on matching colum C (ORDER NO) with cell C7 (ORDER NO) in IN sheet and column E (BRAND) with after C12 cell (BRAND)in IN sheet . then should populate right mark and the others should wrong mark and when pouplate right mark should show zero in column H when right mark is existed in column I for one of the two sheets.

data
sheets(ORDERS SALES,ORDERS PURCHASE, IN)

REP.xlsm
	A	B	C	D	E	F	G	H
1	DATE	ID	ORDER NO	CLIENT REFERENCE	BRAND	QTY	UNIT PRICE	TOTAL
2	11/01/2023	BSJ100	FS-1000	ABBD	BS 1200R20 G580 JAP	20.00	2,000.00	40,000.00
3	12/01/2023	BSJ101	FS-1000	ABBD	BS 1200R20 G580 THI	20.00	2,200.00	44,000.00
4	13/01/2023	BSJ102	FS-1001	XCDFF	BS 1200R20 R187 JAP	25.00	1,990.00	49,750.00
5	14/01/2023	BSJ103	FS-1001	XCDFF	BS 1200R24 G580 JAP	26.00	2,600.00	67,600.00
6	15/01/2023	BSJ100	FS-1001	XCDFF	BS 1200R20 G580 JAP	20.00	2,000.00	40,000.00
7	16/01/2023	BSJ101	FS-1001	XCDFF	BS 1200R20 G580 THI	20.00	2,200.00	44,000.00
8	17/01/2023	BSJ106	FS-1002	AQWE	BS 315/80R22.5 R184 THI	40.00	1,660.00	66,400.00
9	18/01/2023	BSJ106	FS-1003	CVGF	BS 315/80R22.5 R184 THI	10.00	1,670.00	16,700.00
10	19/01/2023	BSJ106	FS-1004	AS-00	BS 315/80R22.5 R184 THI	45.00	1,660.00	74,700.00
11	20/01/2023	BSJ106	FS-1005	AS-01	BS 315/80R22.5 R184 THI	20.00	1,660.00	33,200.00
12	21/01/2023	BSJ106	FS-1006	AS-02	BS 315/80R22.5 R184 THI	10.00	1,660.00	16,600.00
13	22/01/2023	BSJ106	FS-1007	AS-03	BS 315/80R22.5 R184 THI	5.00	1,660.00	8,300.00
14	23/01/2023	BSJ106	FS-1008	AS-04	BS 315/80R22.5 R184 THI	20.00	1,660.00	33,200.00
15	24/01/2023	BSJ106	FS-1009	AS-05	BS 315/80R22.5 R184 THI	15.00	1,660.00	24,900.00
16	25/01/2023	BSJ106	FS-1010	AS-06	BS 315/80R22.5 R184 THI	15.00	1,660.00	24,900.00
17	26/01/2023	BSJ106	FS-1011	AS-07	BS 315/80R22.5 R184 THI	15.00	1,660.00	24,900.00
18	27/01/2023	BSJ106	FS-1012	AS-08	BS 315/80R22.5 R184 THI	40.00	1,660.00	66,400.00
19	28/01/2023	BSJ106	FS-1013	AS-09	BS 315/80R22.5 R184 THI	40.00	1,660.00	66,400.00
20	29/01/2023	BSJ106	FS-1014	AS-10	BS 315/80R22.5 R184 THI	40.00	1,660.00	66,400.00
21	30/01/2023	BSJ107	FS-1014	AS-10	BS 315/80R22.5 R184 JAP	10.00	1,880.00	18,800.00
22	31/01/2023	BSJ107	FS-1015	AS-11	BS 315/80R22.5 R184 JAP	10.00	1,881.00	18,810.00
23	01/02/2023	BSJ100	FS-1015	AS-11	BS 1200R20 G580 JAP	10.00	1,890.00	18,900.00
24	02/02/2023	BSJ103	FS-1015	AS-11	BS 1200R24 G580 JAP	20.00	2,600.00	52,000.00
25	03/02/2023	BSJ107	FS-1016	AS-12	BS 315/80R22.5 R184 JAP	10.00	1,890.00	18,900.00
26	04/02/2023	BSJ103	FS-1016	AS-12	BS 1200R24 G580 JAP	15.00	2,500.00	37,500.00
27	05/02/2023	BSJ100	FS-1016	AS-12	BS 1200R20 G580 JAP	5.00	2,100.00	10,500.00
28	06/02/2023	BSJ101	FS-1016	AS-12	BS 1200R20 G580 THI	3.00	2,050.00	6,150.00
ORDERS SALES

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

REP.xlsm
	C	D	E	F	G	H
1	ORDER NO	CLIENT REFERENCE	BRAND	QTY	UNIT PRICE	TOTAL
2	BBS-00	BGHHY	BS 1200R20 G580 JAP	500.00	1,500.00	750,000.00
3	BBS-00	BGHHY	BS 1200R20 G580 THI	300.00	1,600.00	480,000.00
4	BBS-00	BGHHY	BS 1200R20 R187 JAP	150.00	1,450.00	217,500.00
5	BBS-00	BGHHY	BS 1200R24 G580 JAP	260.00	2,000.00	520,000.00
6	BBS-01	ASDERT	BS 1200R20 G580 JAP	340.00	1,550.00	527,000.00
7	BBS-01	ASDERT	BS 1200R20 G580 THI	800.00	1,440.00	1,152,000.00
8	BBS-01	ASDERT	BS 315/80R22.5 R184 THI	500.00	1,250.00	625,000.00
9	BBS-02	AZSD-00	BS 1200R20 G580 JAP	300.00	1,600.00	480,000.00
10	BBS-03	AZSD-01	BS 1200R20 G580 THI	250.00	1,700.00	425,000.00
11	BBS-04	AZSD-02	BS 1200R20 G580 JAP	300.00	1,800.00	540,000.00
12	BBS-05	AZSD-03	BS 1200R20 G580 THI	350.00	1,900.00	665,000.00
13	BBS-06	AZSD-04	BS 1200R20 G580 JAP	400.00	1,800.00	720,000.00
14	BBS-07	AZSD-05	BS 1200R20 G580 THI	450.00	2,100.00	945,000.00
15	BBS-08	AZSD-06	BS 1200R20 G580 JAP	500.00	2,200.00	1,100,000.00
16	BBS-09	AZSD-07	BS 1200R20 G580 THI	550.00	2,300.00	1,265,000.00
17	BBS-10	AZSD-08	BS 1200R20 G580 JAP	600.00	1,440.00	864,000.00
18	BBS-11	AZSD-09	BS 1200R20 G580 THI	650.00	2,500.00	1,625,000.00
19	BBS-12	AZSD-10	BS 1200R20 G580 JAP	700.00	2,600.00	1,820,000.00
20	BBS-13	AZSD-11	BS 1200R20 G580 THI	750.00	1,330.00	997,500.00
21	BBS-14	AZSD-12	BS 1200R20 G580 JAP	800.00	2,800.00	2,240,000.00
22	BBS-15	AZSD-13	BS 1200R20 G580 THI	850.00	2,900.00	2,465,000.00
23	BBS-16	AZSD-14	BS 1200R20 G580 JAP	900.00	3,000.00	2,700,000.00
24	BBS-17	AZSD-15	BS 1200R20 G580 THI	950.00	1,670.00	1,586,500.00
25	BBS-18	AZSD-16	BS 1200R20 G580 JAP	1,000.00	3,200.00	3,200,000.00
26	BBS-19	AZSD-17	BS 1200R20 G580 THI	1,050.00	3,300.00	3,465,000.00
27	BBS-20	AZSD-18	BS 1200R20 G580 JAP	1,100.00	1,760.00	1,936,000.00
28	BBS-21	AZSD-19	BS 1200R20 G580 THI	1,150.00	3,500.00	4,025,000.00
ORDERS PURCHASE

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

REP.xlsm
	A	B	C	D	E	F
1
2			CLIENT REFERENCE
3			BGHHY
4			DATE
5			10/01/2024
6			ORDER NO
7			BBS-00
8			INVOICE NO
9			BBBS-1000
10
11
12	ITEM	ID	BRAND	QTY	UNIT PRICE	TOTAL
13	1	BSJ100	BS 1200R20 G580 JAP	500.00	1,500.00	750,000.00
14	2	BSJ101	BS 1200R20 G580 THI	300.00	1,600.00	480,000.00
15	3	BSJ102	BS 1200R20 R187 JAP	150.00	1,450.00	217,500.00
16	4	BSJ103	BS 1200R24 G580 JAP	260.00	2,000.00	520,000.00
17
18
19
20	TOTAL			1,210.00		1,967,500.00
IN

Cell Formulas
Range	Formula
C5	C5	=TODAY()
F13:F16	F13	=D13*E13
D20	D20	=SUM(D13:D19)
F20	F20	=SUM(F13:F16)

when matching (ORDER NO) in cell C7 and BRAND in after C12 in IN sheet with (ORDER NO) in column C (ORDER NO) and column E (BRAND) in sheet ORDERS SALES or sheet ORDERS PURCHASE then the result in column H will be zero and I will mark right & wrong for one of the two sheets like this

REP.xlsm
	A	B	C	D	E	F	G	H	I
1	DATE	ID	ORDER NO	CLIENT REFERENCE	BRAND	QTY	UNIT PRICE	TOTAL
2	20/01/2023	BSJ100	BBS-00	BGHHY	BS 1200R20 G580 JAP	500.00	1,500.00	0.00	P
3	21/01/2023	BSJ101	BBS-00	BGHHY	BS 1200R20 G580 THI	300.00	1,600.00	0.00	P
4	22/01/2023	BSJ102	BBS-00	BGHHY	BS 1200R20 R187 JAP	150.00	1,450.00	0.00	P
5	23/01/2023	BSJ103	BBS-00	BGHHY	BS 1200R24 G580 JAP	260.00	2,000.00	0.00	P
6	24/01/2023	BSJ100	BBS-01	ASDERT	BS 1200R20 G580 JAP	340.00	1,550.00	527,000.00	O
7	25/01/2023	BSJ101	BBS-01	ASDERT	BS 1200R20 G580 THI	800.00	1,440.00	1,152,000.00	O
8	26/01/2023	BSJ106	BBS-01	ASDERT	BS 315/80R22.5 R184 THI	500.00	1,250.00	625,000.00	O
9	27/01/2023	BSJ100	BBS-02	AZSD-00	BS 1200R20 G580 JAP	300.00	1,600.00	480,000.00	O
10	28/01/2023	BSJ101	BBS-03	AZSD-01	BS 1200R20 G580 THI	250.00	1,700.00	425,000.00	O
11	29/01/2023	BSJ100	BBS-04	AZSD-02	BS 1200R20 G580 JAP	300.00	1,800.00	540,000.00	O
12	30/01/2023	BSJ101	BBS-05	AZSD-03	BS 1200R20 G580 THI	350.00	1,900.00	665,000.00	O
13	31/01/2023	BSJ100	BBS-06	AZSD-04	BS 1200R20 G580 JAP	400.00	1,800.00	720,000.00	O
14	01/02/2023	BSJ101	BBS-07	AZSD-05	BS 1200R20 G580 THI	450.00	2,100.00	945,000.00	O
15	02/02/2023	BSJ100	BBS-08	AZSD-06	BS 1200R20 G580 JAP	500.00	2,200.00	1,100,000.00	O
16	03/02/2023	BSJ101	BBS-09	AZSD-07	BS 1200R20 G580 THI	550.00	2,300.00	1,265,000.00	O
17	04/02/2023	BSJ100	BBS-10	AZSD-08	BS 1200R20 G580 JAP	600.00	1,440.00	864,000.00	O
18	05/02/2023	BSJ101	BBS-11	AZSD-09	BS 1200R20 G580 THI	650.00	2,500.00	1,625,000.00	O
19	06/02/2023	BSJ100	BBS-12	AZSD-10	BS 1200R20 G580 JAP	700.00	2,600.00	1,820,000.00	O
20	07/02/2023	BSJ101	BBS-13	AZSD-11	BS 1200R20 G580 THI	750.00	1,330.00	997,500.00	O
21	08/02/2023	BSJ100	BBS-14	AZSD-12	BS 1200R20 G580 JAP	800.00	2,800.00	2,240,000.00	O
22	09/02/2023	BSJ101	BBS-15	AZSD-13	BS 1200R20 G580 THI	850.00	2,900.00	2,465,000.00	O
23	10/02/2023	BSJ100	BBS-16	AZSD-14	BS 1200R20 G580 JAP	900.00	3,000.00	2,700,000.00	O
24	11/02/2023	BSJ101	BBS-17	AZSD-15	BS 1200R20 G580 THI	950.00	1,670.00	1,586,500.00	O
25	12/02/2023	BSJ100	BBS-18	AZSD-16	BS 1200R20 G580 JAP	1,000.00	3,200.00	3,200,000.00	O
26	13/02/2023	BSJ101	BBS-19	AZSD-17	BS 1200R20 G580 THI	1,050.00	3,300.00	3,465,000.00	O
27	14/02/2023	BSJ100	BBS-20	AZSD-18	BS 1200R20 G580 JAP	1,100.00	1,760.00	1,936,000.00	O
28	15/02/2023	BSJ101	BBS-21	AZSD-19	BS 1200R20 G580 THI	1,150.00	3,500.00	4,025,000.00	O
ORDERS PURCHASE

Cell Formulas
Range	Formula
H6:H28	H6	=F6*G6

see the attached picture becuase XL2bb tool doesn't show RIGHT,WRONG mark

I hope somebody help me .

 

[awoohaw]

Try this:

Book1
	A	B	C	D	E	F	G	H	I	J
1	DATE	ID	ORDER NO	CLIENT REFERENCE	BRAND	QTY	UNIT PRICE	TOTAL	FORMAT COLUMN AS Wingdnings 2	Use these formulas in cells in column H and I:
2	2023-01-20	BSJ100	BBS-00	BGHHY	BS 1200R20 G580 JAP	500	1500	0	P	=IF(AND(IN!$C$7=C2,ISNUMBER(MATCH($E2,IN!$C$3:$C$300,0))),0,F2*G2)
3	2023-01-21	BSJ101	BBS-00	BGHHY	BS 1200R20 G580 THI	300	1600	0	P	=IF(AND(IN!$C$7=C2,ISNUMBER(MATCH($E2,IN!$C$3:$C$300,0))),"P","O")
4	2023-01-22	BSJ102	BBS-00	BGHHY	BS 1200R20 R187 JAP	150	1450	0	P
5	2023-01-23	BSJ103	BBS-00	BGHHY	BS 1200R24 G580 JAP	260	2000	0	P
6	2023-01-24	BSJ100	BBS-01	ASDERT	BS 1200R20 G580 JAP	340	1550	527000	O
7	2023-01-25	BSJ101	BBS-01	ASDERT	BS 1200R20 G580 THI	800	1440	1152000	O
8	2023-01-26	BSJ106	BBS-01	ASDERT	BS 315/80R22.5 R184 THI	500	1250	625000	O
9	2023-01-27	BSJ100	BBS-02	AZSD-00	BS 1200R20 G580 JAP	300	1600	480000	O
10	2023-01-28	BSJ101	BBS-03	AZSD-01	BS 1200R20 G580 THI	250	1700	425000	O
OrdersPurchase

Cell Formulas
Range	Formula
H2:H10	H2	=IF(AND(IN!$C$7=C2,ISNUMBER(MATCH($E2,IN!$C$3:$C$300,0))),0,F2*G2)
I2:I10	I2	=IF(AND(IN!$C$7=C2,ISNUMBER(MATCH($E2,IN!$C$3:$C$300,0))),"P","O")
J2	J2	=FORMULATEXT(H2)
J3	J3	=FORMULATEXT(I2)

 

[abdo meghari]

wow !
I thought to need to vba , but the formula seem amazing
thank you so much

 

[awoohaw]

happy to help! Best Wishes!