conditional format duplicates based on two columns

Question

Hi all,

this should be simple, but I'm havng difficulty. I have a rather long worksheet, with a first name in column A, and a last name in column B. I'd like to conditionally format both columns if a first AND last name are duplicated on the same row.

e.g.:

col A            col B

John             Smith

John             Jones

John             Smith

so in the above example, both "John Smith"s would be highlighted.

Any help would be appreciated!

Jim

Answer 1

... so in the above example, both "John Smith"s would be highlighted. 

Assuming that your data is in column A (first name) and column B (last name) with or without a header row like this,

              

You can create a conditional formatting rule based on a formula by selecting all of columns A and B with A1 as the active cell and using this,

=AND(LEN($A1&$B1),COUNTIFS($A:$A,$A1,$B:$B,$B1)>1)

That is pretty CPU intensive, even for a background operation like CF so it would be better to tap F5 and type A1:B9999 as the Reference: and hit OK. With A1:B9999 selected you can use this altered formula,

=AND(LEN($A1&$B1),COUNTIFS($A$1:$A$9999,$A1,$B$1:$B$9999,$B1)>1)

Modify row 9999 to suit your own data.

Either way, select a red fill as the Format and click OK all the way out.

Answer 2

Thanks so much! Just what I needed.