How to identify duplicates using another column

**NKRA** · 07-19-2010, 09:24 AM

I have a excel sheet with many rows and in one of the column i have text. ( Text is almost like paragraph style).

Many of these text are duplicates.

I want create a indicator in another column indicating it is duplicate or unique identifies

sample attached.

Thnx
NKRA

**NBVC** · 07-19-2010, 09:34 AM

Because of the size of text strings in the cells, we can't use COUNTIF()

as =IF(COUNTIF(A:A,A2)>1,"DUPLICATE","NOT DUPLICATE")

so we have to use an alternative:

=IF(SUMPRODUCT(--($A$1:$A$100=A2))>1,"Duplicate","Not Duplicate")

which needs fixed ranges (in pre XL2007)... copied down.

**NKRA** · 07-20-2010, 04:29 PM

Thnx for solution and explanation. Now i understand why i got value error on countif formula.

Further to above i have enhancement required on solution (excel attached) I need to get field 3 in which if there is Duplicate in subsequent finding bring blank.

Thnx
NKRA

**NBVC** · 07-20-2010, 04:37 PM

Try:

=IF(SUMPRODUCT(--(A$2:A2=A2))>1,"",IF(SUMPRODUCT(--($A$1:$A$100=A2))>1,A2,""))

note that A4 and A5 are not duplicates.. something about them is not exact.

Note: Is the space after Linda Fisher-Lewis, It appears as though one uses CHAR(160) and the other CHAR(32)... where they copied from different sources... there could be other spaces like that in those strings.