Duplicate entries in column

[Kevin]

Hi How do I find duplicate entries in a column, I have
over 4000 bar code numbers in column and I know that some
have been duplicated.

any help appreciated.

 

[Frank Kabel]

Hi
see:
http://www.cpearson.com/excel/duplicat.htm#TaggingDuplicates

 

[Alan]

A good way I think is to use Advanced Filter and filter for Unique Entries
as it doesn't involve using formulas or code. There is a good tutorial on
Debra Dalgleish's site, see the link below. Once you've done the filtering
operation you can just Copy > Paste it back to the original location.
http://www.contextures.com/xladvfilter01.html#FilterUR
Regards,

 

[X_HOBBES]

Frank's response is a great way!

Another way of doing it is to first, sort your data based on the
barcode column. Then, insert a "Duplicate Flag" column next to it and
use a formula such as

=if(A2=A3,"Duplicate", if(A2=A1,"Duplicate",""))

where column A has your barcode. This will flag all duplicates as
long as they are sorted

However, the solution in Frank's posting doesn't require sorting. For
the same example above, the formula would be

=if(countif(A:A,A2),"Duplicate","")

Simpler and more versatile -- I love it!

X_HOBBES

 

[Myrna Larson]

RE: =if(countif(A:A,A2),"Duplicate","")

I think that formula had better be

=if(countif(A:A,A2)>1,"Duplicate","")

or, if you don't want to flag the first occurrence:

=IF(COUNTIF($A$1:A2)>1,"Duplicate","")

 

[Frank Kabel]

Hi Myrna
the last formula probably should read:
=IF(COUNTIF($A$1:A2,A2)>1,"Duplicate","")

)

 

[Myrna Larson]

I think you're right <g>. An A2 got lost somewhere in translation.