Three-way Conditional Formatting for Duplicates?

**Hyflex** · 10-19-2011, 04:59 AM

Hey,

I'm having a little issue with some conditional formatting not sure what the best way to go... I'm using the following:

=COUNTIF(A:A,A2)>1

I need it to check, Column A:A for duplicates if one is a dupelicate then it needs to check if the column B:B on the same row is a dupelicate and then if they still match it needs to check column c:c if all 3 match then I need it to highlight the row...

I don't know if this is easier doing in a macro.... if it is easier doing it in a macro then I would want it to check all Columns from A to J and if they all match then it needs to remove the entire row.

So:

A		B		C
King		55		End <-- THIS ROW MATCHS ALL 3
Jam		91		End
King		55		Start
Lome		91		End
King		55		End <-- THIS ROW MATCHS ALL 3 (HIGHLIGHT OR REMOVE THIS ROW)

**TMS** · 10-19-2011, 05:21 AM

The condition is:

=AND(COUNTIF($A:$A,$A2)>1,COUNTIF($B:$B,$B2)>1,COUNTIF($C:$C,$C2)>1)

Regards

**Hyflex** · 10-19-2011, 05:25 AM

Originally Posted by TMShucks

The condition is:

=AND(COUNTIF($A:$A,$A2)>1,COUNTIF($B:$B,$B2)>1,COUNTIF($C:$C,$C2)>1)

Regards

Could it be converted into a macro by any chance to remove the row if all are matchs?

**daddylonglegs** · 10-19-2011, 05:32 AM

Using three COUNTIFS won't check if the three values are all repeated on another row, that just checks whether the values are repeated, possibly on separate rows.

To check for repeats of the whole row I suggest you need COUNTIFS, this version will highlight all matches (e.g. if there are 3 matching rows it will format all 3)

=COUNTIFS($A:$A,$A1,$B:$B,$B1,$C:$C,$C1)>1

or to format all repeats after the first

=COUNTIFS($A$1:$A1,$A1,$B$1:$B1,$B1,$C$1:$C1,$C1)>1

**TMS** · 10-19-2011, 05:34 AM

Put the formula into a helper column. This will highlight the duplicates with TRUE. Filter on the helper column for TRUE. Delete the visible records.

If you want a macro, record the actions you take for the basic code; you'll need to tidy it up a bit to generalise it and make it more efficient.

It's not clear to me if you want to delete all duplicates or leave one live. Might be easier to do if they're sorted and compare values between rows.

Regards