CountIf duplicate in other instances, but not first instance [SOLVED]

[jassybun]

Hey guys! I want this formula to only specify "Duplicate" on all other instances of the duplicate except for the first one. Right now it is showing all instances.


=IF(COUNTIF(W:W,W14)>1,"Duplicate","")


so it would show


1234....... (blank)

1234....... Duplicate

1236........(blank)


INSTEAD of what it is doing now:


1234....... Duplicate

1234....... Duplicate

1236........(blank)

 

[NARAYANK991]

Hi ,


Try this :


=IF(COUNTIF($W$3:$W$23,W3)=1,"",IF(COUNTIF($W$3:W3,W3)>1,"Duplicate",""))


Narayan

 

[jassybun]

Excellent!~thank you so much

 

[Colin Legg]

If you sort your data by column W then you can make the formula much simpler and faster:


In row 3 (assuming row 3 is the first row of data)

=IF(W3=W2,"Duplicate","")

and then fill down.

 

[Debraj]

Just trying to shorten the length of Narayan formula.. little bit..

=IF(COUNTIF($W$3:W3,W3)-1,"Duplicate","")

Regards,

Deb