How do I compare and highlight the matches from two columns in Excel

[Vpremakumar]

I have two columns of data in an Excel spreadsheet. I have to see that every value in Column A has a match in Column B. I understand that two columns can be compared and highlighted in Conditional Formatting using the Match function.


The problem is that the the Match function in Conditional Formatting does not exclude previously matched and highlighted values from the list for future comparisons. For example...


A: 1, 1, 2, 3


B: 2, 3, 1, 0


If using the Match function in Conditional Formatting, both "1" values in Column A would be highlighted because it sees that there is a "1" in column B. I need the columns to match one-to-one. So once a value is found to have a match in the other column, that match is excluded from the comparisons for the rest of the function's execution.

 

[NARAYANK991]

Hi Premakumar ,


You can use the technique explained here :


http://chandoo.org/wp/2011/10/27/compare-2-lists-visually-and-highlight-matches/


Basically , you use helper columns to concatenate the item in one column along with its count ; thus if there are two entries which are the same , the first entry will become entry1 , while the second entry will become entry2 ; do the same with the second column ; now when you try to match , entry1 in the first column will match only with entry1 in the second column ; entry2 from the first column will not match entry1 in the second column.


Narayan