Help with comparing columns matches

Brass Contributor

I'm betting this is an easy one, but I can't figure it out.  I have a large sheet with 3 columns.  Going from left to right each column will have something from the column on its left but some extra cells as well.  I want to compare each column to the one to the left of it and highlight the matches differences from one column to the next however i only want to highlight the matches in the right column, not both.


For example, below compare Col B to Col A and highlight the matches (John and Jane) but only highlight them in Col B.  Then compare Col C to Col B for matches and highlight the matches (John, Jane and Peter) but again only highlight them in Col C.  Screen shot of what I mean is attached.


Note I did just use the conditional formatting tool to highlight the matches and it worked fine when comparing Col A and Col B but it highlights the matches in both columns so when I compared Col B and Col C everything in Col B is highlighted because everything in Col B appears in Col C.  This is why I only want to highlight the matches in the column to the right.  Basically, it shows me what's the same from the last column so I can zero in on the differences.


Col ACol BCol C
2 Replies

@lfk73 Try the following conditional formatting rule. Download the attached file.






Thanks that kind of works. It does match cells from col A to B to C but it also seems to be missing some.

Take a look at the attached in column D I've put a ? next to a row I expected to match. Row 4, 5 and 9 I would expect the cell in col C to be formatted. Row 7 col C also matches cells in row 8 col A and B. Row 9 i expected B9 to also be formatted matching A9 but it's not. I check formatting etc can't find why it won't match.