Conditional formatting for comparing cells

%3CLINGO-SUB%20id%3D%22lingo-sub-2745926%22%20slang%3D%22en-US%22%3EConditional%20formatting%20for%20comparing%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2745926%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%202000%2B%20row%20spreadsheet%20that%20I%20have%20columns%20that%20compared%20with%20v-lookup.%20I%20copied%20and%20pasted%20values%20for%20the%20lookup%20in%20a%20new%20column%20and%20now%20I%20need%20to%20highlight%20the%20rows%20that%20don't%20match.%20I%20have%20tried%20conditional%20formatting%20with%20the%20add%20new%20rule%26gt%3BUse%20a%20formula%20to%20determine%20which%20cells%20to%20format%20and%20the%20formula%20I%20use%20is%20%3D%24A%3A%24A%26lt%3B%26gt%3B%24B%3A%24B%20and%20click%20OK%20(I%20don't%20care%20what%20the%20colors%20are).%20It%20isn't%20working.%20There%20are%20obvious%20differences%2C%20but%20none%20of%20my%20cells%20are%20highlighted.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20sample%20I%20have%20loaded%20contains%2018%20rows%20of%20the%20spreadsheet%20and%20I%20need%20to%20compare%20columns%20A%20to%20B%20and%20highlight%20each%20row%20that%20is%20different...ie%20A3%20is%20obviously%20different%20than%20B3.%20I%20would%20like%20to%20create%20a%20rule%20that%20would%20compare%20A3%20to%20B3%20and%20let%20me%20know%20that%20it%20is%20different%20so%20I%20can%20go%20change%20it%20in%20a%20database.%3C%2FP%3E%3CP%3EI%20hope%20I%20am%20clear%20on%20what%20I%20want%20to%20do%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2745926%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2745978%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20for%20comparing%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2745978%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1154425%22%20target%3D%22_blank%22%3E%40KellySingleton%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Column%20B%20%22visually%22%20looks%20the%20same%20values%20of%20Column%20A%3CBR%20%2F%3EBut%20you%20have%20a%20lot%20of%20%22extra%22%20blank%20spaces%20on%20column%20B.%3CBR%20%2F%3ESo%20in%20this%20case%20is%20recommended%20remove%20these%20extra%20spaces%20by%20using%20the%20TRIM()%20formula.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DISERROR(MATCH(TRIM(%24B1)%2C%24A%241%3A%24A%2419%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2745990%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20for%20comparing%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2745990%22%20slang%3D%22en-US%22%3EI%20ran%20the%20formula%20but%20then%20how%20do%20I%20get%20the%20cells%20highlighted%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2746008%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20for%20comparing%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2746008%22%20slang%3D%22en-US%22%3EHave%20a%20look%20on%20the%20attached%20file.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a 2000+ row spreadsheet that I have columns that compared with v-lookup. I copied and pasted values for the lookup in a new column and now I need to highlight the rows that don't match. I have tried conditional formatting with the add new rule>Use a formula to determine which cells to format and the formula I use is =$A:$A<>$B:$B and click OK (I don't care what the colors are). It isn't working. There are obvious differences, but none of my cells are highlighted.

 

The sample I have loaded contains 18 rows of the spreadsheet and I need to compare columns A to B and highlight each row that is different...ie A3 is obviously different than B3. I would like to create a rule that would compare A3 to B3 and let me know that it is different so I can go change it in a database.

I hope I am clear on what I want to do

7 Replies

@KellySingleton 

The Column B "visually" looks the same values of Column A
But you have a lot of "extra" blank spaces on column B.
So in this case is recommended remove these extra spaces by using the TRIM() formula.

=ISERROR(MATCH(TRIM($B1),$A$1:$A$19,0))

 

I ran the formula but then how do I get the cells highlighted?
Have a look on the attached file.
Got it - Thanks

I must have something else wrong when I converted the formula to the actual worksheet. Not all of the non-matching cells highlighted. ie G3 & H3 or G4 & H4. See attached. @Juliano-Petrukio 

@KellySingleton 

It looks all correct.

I made a small change on your file to optmise it.

Your file is very big even with a small quantity of data.

The main reason is because you expanded the formulas to the all rows of the spreadsheet. Plus than1 million rows (1,048,576). 

Thank you but apparently I did not convey what I needed exactly. I need differences in each row in those two columns highlighted. G3 and H3 are different, so I would like one of those cells to highlight telling me they are not a match. G2 & H2 are the same so they would not highlight.