Comparing 2 columns for matches or differences

[Peter Barron]

I have 2 columns of text. Column A has names, and column B has roles. Some of the names in column A have different roles in column B (which is what I want to find) as these need to be all the same role.

Example:
Col A --------- Col B
John ---------- Nurse
Nancy -------- Advocate
John ---------- Advocate
Fred ---------- Nurse

...and so on.

In the above example, I would want to identify all instances where "John" is showing a different value in Col B (in this case "Nurse" and "Advocate"), and John should always have "Nurse" next to his name.

I've looked at conditional formatting, VLOOKUP, MATCH, and INDEX formulas with no luck. Any ideas are appreciated!

 

[AlanSidman]

How about creating a pivot table. This will group all Names and their roles and be easier to see.

 

[Peter Barron]

Thanks Alan. I use pivots all the time and had not thought to apply that to this problem (thinking there was a formula that would do the trick!). I'll give that a go...thanks!