How to compare 2 columns with lists in excel and highlight the non matching values?

0
votes

I have to Compare 2 lists in excel file and highlight the non matching entry corresponding to the cell value it was compared with. I was doing this for data validation purpose.

Example:

Column 1
1142103
36300
72600
121000
36300
60500
36300
3025
3025
8250

Column 2
230,000
36,300
72,600
121,000
36,300
60,500
36,300
3,025
***3,024***
8,250

Column 1 should be compared with column 2 and the non matching value in the 9th row/cell should be highlighted.

I have tried column difference option available in Home-Editing-Find&Select-GoToSpecial-Column Differences but that doesn't show the correct result since one column values have commas in them.

I tried to put commas to other column as well and use the column difference but still no use. I feel conditional formatting would be one option but I was able to compare only one row using the formula $A1<>$B1 and highlight with desired format, but this doesn't work for a range of cells.

1
vbaexcelconditional-formatting

1 Answers

1
votes

Actually, it would be the opposite: compare column 2 to column 1.

You can use the built-in match function.

Suppose column 1 is "A" & column 2 is "B", in a third column "C", you can try:

=if(iserror(match(b2,$A$2:$A$11,0)),0,1)

That is, if there is no match, the output will be 0, and 1 otherwise. Then, you can apply a simple conditional format for Column C, where you highlight the cell if = 0.

P.S: there are almost always multiple solutions to an issue.