How do I look for dupliactes within the same file?

[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?

[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




"skippy_prine" wrote:

> 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?

[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.
"skippy_prine" wrote:

> 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?

[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.

"skippy_prine" wrote:

> 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?

[Michael]

You can also visit Chip Pearsons' site:
http://www.cpearson.com/excel/duplicat.htm

--
Sincerely, Michael Colvin


"skippy_prine" wrote:

> 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?