Conditional Formatting - Formula Needs Correction

RAJESH1960

Well-known Member
Joined
Mar 26, 2020
Messages
743
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGHI
1LineGSTINxxxxSupplierInvoice numberInvoice DateInvoice Value (₹)As PerRemarks
21166129AAFCC9473R1ZLxxxxxxx32202091909-11-20203916.15TALLYThis is wrong
368727AAFCC9473R1ZPxxxxxxx32202091909-11-20203914.27GSTR 2AThis is wrong
4285924ACRPP1782B1ZKxxxxxxxO-00222-08-20203304.00GSTR 2AThis is wrong
57365Not Availablexxxxxxx00223-08-20203304.00TALLYThis is wrong
61223929AAFCC9473R1ZLxxxxxxx44613073406-03-20213089.79TALLYThis is wrong
7127129AAFCC9473R1ZLxxxxxxx44613073406-03-20213087.47GSTR 2AThis is wrong
810704Not Availablexxxxxxx31-08-20203083.66TALLY
91196729AAFCC9473R1ZLxxxxxxx32202275108-01-20212899.79TALLY
1010553Not Availablexxxxxxx01-04-20202723.13TALLY
1110708Not Availablexxxxxxx30-09-20202666.92TALLY
1238629ABDFA8515C1ZNxxxxxxxASI-0002/20-2105-08-20202595.16GSTR 2ACorrect
13718329ABDFA8515C1ZNxxxxxxx000205-08-20202595.16TALLYCorrect
1410692Not Availablexxxxxxx31-05-20202327.64TALLY
15885629AADFM5947K1ZQxxxxxxx151402-01-20212269.71TALLY
1611763Not Availablexxxxxxx20000261201-12-20202242.08TALLY
17383609AATFR8142F1ZPxxxxxxxRT2000261224-11-20202240.40GSTR 2A
18546029AAIFR2782A1Z6xxxxxxxRS/202022/20-2108-02-20212225.58GSTR 2AThis is wrong
19497929AAIFR2782A1Z6xxxxxxxRS/202022/20-2109-02-20212225.58GSTR 2AThis is wrong
209346Not Availablexxxxxxx20202208-02-20212225.58TALLY
21281629AGDPS5725R1ZHxxxxxxx2021048924-09-20202124.00GSTR 2A
221146329AGDP85725R1ZHxxxxxxx2021048924-09-20202124.00TALLY
23545929AAIFR2782A1Z6xxxxxxxRS/201961/20-2102-02-20212122.19GSTR 2AThis is wrong
24497829AAIFR2782A1Z6xxxxxxx20196103-02-20212122.19GSTR 2AThis is wrong
259270Not Availablexxxxxxx20196102-02-20212122.19TALLY
26546129AAIFR2782A1Z6xxxxxxxRS/202167/20-2123-02-20212007.77GSTR 2AThis is wrong
27498029AAIFR2782A1Z6xxxxxxxRS/202167/20-2123-02-20212007.77GSTR 2AThis is wrong
289542Not Availablexxxxxxx20216723-02-20212007.77TALLY
29260229AACFA5793L1ZXxxxxxxx910-08-20202006.00GSTR 2ACorrect
301131829AACFA5793L1ZXxxxxxxx910-08-20202006.00TALLYCorrect
3165307AANFJ3039R1ZOxxxxxxx***/20-21/06028-11-20201966.09GSTR 2ACorrect
321174207AANFJ3039R1ZOxxxxxxx06001-12-20201966.09TALLYCorrect
Query to Match amount
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G1:G32,G12384:G1048576Expression=COUNTIFS($B$1:$B$3686,$B1,$G$1:$G$3686,"<"&$G1+0.5,$G$1:$G$3686,">"&$G1-0.5)=1textNO
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Forum statistics

Threads
1,148,334
Messages
5,746,154
Members
423,994
Latest member
blzxatly

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top