Conditional Formatting - Formula Needs Correction

[RAJESH1960]

Hello guys

I have this data of around 12000 rows. I have to match the amounts of GSTR 2A and Tally. So, I applied a formula to match the amounts using conditional formatting. But I am facing a few mistakes.

The conditional formatting should color the cells which are not a match. There are a few conditions for the formula to accept. 1. Invoice value is a must match with a nominal difference of not more than 2/-. 2. If either of the columns B D and E are the same as in GSTR 2A & TALLY and the Invoice value is a match with a nominal difference of not more than 2/-.

The remarks column shows that the formatting has not worked correctly in most cases as the H column has both GSTR 2A in both cases, whereas it should have one GSTR 2A and one TALLY.

Query to match amount.xlsx
	A	B	C	D	E	F	G	H	I
1	Line	GSTIN	xxxx	Supplier	Invoice number	Invoice Date	Invoice Value (₹)	As Per	Remarks
2	11661	29AAFCC9473R1ZL		xxxxxxx	322020919	09-11-2020	3916.15	TALLY	This is wrong
3	687	27AAFCC9473R1ZP		xxxxxxx	322020919	09-11-2020	3914.27	GSTR 2A	This is wrong
4	2859	24ACRPP1782B1ZK		xxxxxxx	O-002	22-08-2020	3304.00	GSTR 2A	This is wrong
5	7365	Not Available		xxxxxxx	002	23-08-2020	3304.00	TALLY	This is wrong
6	12239	29AAFCC9473R1ZL		xxxxxxx	446130734	06-03-2021	3089.79	TALLY	This is wrong
7	1271	29AAFCC9473R1ZL		xxxxxxx	446130734	06-03-2021	3087.47	GSTR 2A	This is wrong
8	10704	Not Available		xxxxxxx		31-08-2020	3083.66	TALLY
9	11967	29AAFCC9473R1ZL		xxxxxxx	322022751	08-01-2021	2899.79	TALLY
10	10553	Not Available		xxxxxxx		01-04-2020	2723.13	TALLY
11	10708	Not Available		xxxxxxx		30-09-2020	2666.92	TALLY
12	386	29ABDFA8515C1ZN		xxxxxxx	ASI-0002/20-21	05-08-2020	2595.16	GSTR 2A	Correct
13	7183	29ABDFA8515C1ZN		xxxxxxx	0002	05-08-2020	2595.16	TALLY	Correct
14	10692	Not Available		xxxxxxx		31-05-2020	2327.64	TALLY
15	8856	29AADFM5947K1ZQ		xxxxxxx	1514	02-01-2021	2269.71	TALLY
16	11763	Not Available		xxxxxxx	200002612	01-12-2020	2242.08	TALLY
17	3836	09AATFR8142F1ZP		xxxxxxx	RT20002612	24-11-2020	2240.40	GSTR 2A
18	5460	29AAIFR2782A1Z6		xxxxxxx	RS/202022/20-21	08-02-2021	2225.58	GSTR 2A	This is wrong
19	4979	29AAIFR2782A1Z6		xxxxxxx	RS/202022/20-21	09-02-2021	2225.58	GSTR 2A	This is wrong
20	9346	Not Available		xxxxxxx	202022	08-02-2021	2225.58	TALLY
21	2816	29AGDPS5725R1ZH		xxxxxxx	20210489	24-09-2020	2124.00	GSTR 2A
22	11463	29AGDP85725R1ZH		xxxxxxx	20210489	24-09-2020	2124.00	TALLY
23	5459	29AAIFR2782A1Z6		xxxxxxx	RS/201961/20-21	02-02-2021	2122.19	GSTR 2A	This is wrong
24	4978	29AAIFR2782A1Z6		xxxxxxx	201961	03-02-2021	2122.19	GSTR 2A	This is wrong
25	9270	Not Available		xxxxxxx	201961	02-02-2021	2122.19	TALLY
26	5461	29AAIFR2782A1Z6		xxxxxxx	RS/202167/20-21	23-02-2021	2007.77	GSTR 2A	This is wrong
27	4980	29AAIFR2782A1Z6		xxxxxxx	RS/202167/20-21	23-02-2021	2007.77	GSTR 2A	This is wrong
28	9542	Not Available		xxxxxxx	202167	23-02-2021	2007.77	TALLY
29	2602	29AACFA5793L1ZX		xxxxxxx	9	10-08-2020	2006.00	GSTR 2A	Correct
30	11318	29AACFA5793L1ZX		xxxxxxx	9	10-08-2020	2006.00	TALLY	Correct
31	653	07AANFJ3039R1ZO		xxxxxxx	JAP/20-21/060	28-11-2020	1966.09	GSTR 2A	Correct
32	11742	07AANFJ3039R1ZO		xxxxxxx	060	01-12-2020	1966.09	TALLY	Correct
Query to Match amount

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G1:G32,G12384:G1048576	Expression	=COUNTIFS($B$1:$B$3686,$B1,$G$1:$G$3686,"<"&$G1+0.5,$G$1:$G$3686,">"&$G1-0.5)=1	text	NO