How do I look for dupliactes within the same file?

S

skippy_prine

I am trying to spot possible duplicate numbers in a ver large file. Is there
a formaula I could use to make sure none of these 13 digit account numbers
are listed twice within the same file?
 
C

CLR

If this is something you have to do regularly, Jim Cone's commercial Add-in
called XLConpanion makes this job a breeze............


Vaya con Dios,
Chuck, CABGx3
 
S

Suresh

A quick solution: Sort the entire data by the field you want to find. add a
helper column put a formula to check if the adjacent cells are the same like
=A1=A2. If you copy this down the column, all duplicates will show a True.
Put an auto filter. Filter for true and delete those rows. What you will be
left with will be only unique records. There may be a better/easier solution
available which I am not aware
of.
 
S

Sloth

=SUMIF(A:A,"="&A1)/A1
will tell you how many times A1 appears in the list.

But you would probably like conditional formating better. Highlight the
list and select Format->Conditional Formatting and select "Formula Is".
Insert this formula
=SUMIF(A:A,"="&A1)/A1>1
and format it with something obvious like a green background color. Include
both the '=' and the '>' signs. A1 is the top-left cell in the highlighted
area. Click "Okay". Every duplicate will now be highlighted green. Hope
this helps.
 
Top