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?

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?

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?

=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)/A11
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?

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?