Format duplicates for more cols

[agibsonsw]

Hello. Excel 2007.
Is it possible to create a formula to use with Conditional Formatting, to colour cells if there are
duplicates across three of the columns? For example, to find customers with the same FirstName, Surname,
and Title? Thanks, Andy.

[HansV]

Yes. Let's say the data are in A2:C100
Select the data range, and note the address of the active cell within the selection; I'll assume it is A2.
Click Conditional Formatting.
Click New Rule...
Click "Use a formula to determine which cells to format".
Enter either of the following formulas; the first works in any version of Excel, the second only in Excel 2007 or later:

=SUMPRODUCT(($A$2:$A$100=$A2)*($B$2:$B$100=$B2)*($C$2:$C$100=$C2))>1

or

=COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2,$C$2:$C$100,$C2)>1

Note the use of absolute and relative references.

Click Format... to specify the desired formatting.
OK your way out.

[agibsonsw]

Hi. Thanks, I'll try that in a moment.
But do the duplicates occur in the same row? Meaning Mr, John Smith occurs in another row?
(Hope this makes sense?) Andy.

[HansV]

I had assumed that you had three columns, one for first name, another for surname, and a third for title, and that you wanted to highlight a row if there is at least one other row with the same first name AND surname AND title.

If that is not what you wanted, can you provide more info? Thanks.

[agibsonsw]

Hello.
That is what I'm after. I've checked it and it works brilliantly. Ta, Andy.