Forum Discussion
Help with comparing columns matches
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 A | Col B | Col C |
| John | John | John |
| Jane | Jane | Jane |
| Peter | Peter | |
| Paul |
2 Replies
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.
