Conditional formatting for multiple duplicate cells

[zombiemaster]

So management has this spreadsheet ... sounds like the beginning of a really bad joke, right?

But it's true.

We have a spreadsheet with hundreds of rows of call details. Date, name, policy number, caller name, etc. They want to know if there has been calls regarding the same policy previously, by highlight the policy number if it's a "duplicate call". I am not great at conditional formatting, so this forum is the first place I thought of for help...

We have Last Name in column D First Name in column E and Policy Number in column F. If all three of those are duplicated in the rows above, they want all those rows to highlight the policy number in column F.

Any help is appreciated, and feel free to add your own bad jokes!

~ZM~

 

[Toadstool]

Hi Zombiemaster,

In its simplest form this would highlight all duplicates but of course they'd all be highlit with the same format.

You may want to consider adding a column with just

=COUNTIFS($D$2:$D$9999,D2,$E$2:$E$9999,E2,$F$2:$F$9999,F2)

so you can see how many calls have all three matched.

Book1
	D	E	F
1	Last Name	First Name	Policy No.
2	Smith	Joe	27
3	Smith	Jim	33
4	Jones	Sue	66
5	Smith	James	55
6	Smith	Sue	66
7	Jones	Jim	77
8	Jones	James	55
9	Jones	Sue	66
10	Jones	Jim	77
11	Jones	Jim	88
12
13
14
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:F9999	Expression	=COUNTIFS($D$2:$D$9999,D2,$E$2:$E$9999,E2,$F$2:$F$9999,F2)>1	text	NO

 

[zombiemaster]

That worked perfectly - thank you!!

~ZM~

 

[Toadstool]

You're welcome!
Thanks for the feedback.