I'm trying to identify rows that are duplicates based on data in 3 columns.

A B C
1 ANT BEE NO
2 ANT BEE
3 DEF FUN
4 ANT BEE NO
5 DEF FUN NO
6 EAR RUN NO
7 DEF FUN NO
8 EAR RUN

The formula I have:
=IF(MAX(COUNTIF($A$1:$A$8,A1)*($B$1:$B$8,B1)*($C$1 :$C$8,C1))1,"Duplicates","No Duplicates")

Can someone make this work or have a better solution?

Thanks in advance.
Cue

On 20 Jun., 23:50, Cue wrote:
I'm trying to identify rows that are duplicates based on data in 3 columns.

* * * * A * * * B * * * C
1 * * * ANT * * BEE * * NO
2 * * * ANT * * BEE * *
3 * * * DEF * * FUN * *
4 * * * ANT * * BEE * * NO
5 * * * DEF * * FUN * * NO
6 * * * EAR * * RUN * * NO
7 * * * DEF * * FUN * * NO
8 * * * EAR * * RUN * *

The formula I have:
=IF(MAX(COUNTIF($A$1:$A$8,A1)*($B$1:$B$8,B1)*($C$1 :$C$8,C1))1,"Duplicates"*,"No Duplicates")

Can someone make this work or have a better solution?

Thanks in advance.
Cue

Hi Cue

Try if this is what you need:

=IF(SUMPRODUCT(($A$1:$A$8=A1)*1,($B$1:$B$8=B1)*1,( $C$1:$C
$8=C1)*1)1,"Duplicates","No duplicates")

Regards,
Per

Great!

Thanks for the solution Per!
--
Cue


"Per Jessen" wrote:

On 20 Jun., 23:50, Cue wrote:
I'm trying to identify rows that are duplicates based on data in 3 columns.

A B C
1 ANT BEE NO
2 ANT BEE
3 DEF FUN
4 ANT BEE NO
5 DEF FUN NO
6 EAR RUN NO
7 DEF FUN NO
8 EAR RUN

The formula I have:
=IF(MAX(COUNTIF($A$1:$A$8,A1)*($B$1:$B$8,B1)*($C$1 :$C$8,C1))1,"Duplicates"Â*,"No Duplicates")

Can someone make this work or have a better solution?

Thanks in advance.
Cue

Hi Cue

Try if this is what you need:

=IF(SUMPRODUCT(($A$1:$A$8=A1)*1,($B$1:$B$8=B1)*1,( $C$1:$C
$8=C1)*1)1,"Duplicates","No duplicates")

Regards,
Per