Identifing duplicate records

[Jas]

Hello,
I have a list with thousands of member's names and each member should
have a unique ME seven digit number. Some of those are assigned
incorrectly and therefore are duplicates. How can I identify duplicate
records and, if possible, mark them or display them separately from
the rest? I've been using IF statements but that is time consuming
since I have to scroll down and check for TRUE or FALSE on hundreds of
pages. Any help is greatly appreciated.
Thanks,
Jas

 

[Jim]

One way:
In a separate column =COUNTIF(Range,Cell)>1

 

[Guest]

Jas
After putting in your IF statement, or the COUNTIF that Jim provided, you could sort on that column to seperate out the duplicates

If you need to return the file to its original sequence you can set up a helper column before you sort. In the first blank column adjacent to your data enter 1 in the top cell and 2 in the next cell. Select both cells and double click on the fill handle, the black square in the lower right hand corner of selection. This will fill the series to the bottom of your data. Then after you sort you can resort on this column to return the list to its original sequence

This double click functionality will also copy down your formula

Good Luck
Mark Graesse
(e-mail address removed)

----- Jas wrote: ----

Hello
I have a list with thousands of member's names and each member shoul
have a unique ME seven digit number. Some of those are assigne
incorrectly and therefore are duplicates. How can I identify duplicat
records and, if possible, mark them or display them separately fro
the rest? I've been using IF statements but that is time consumin
since I have to scroll down and check for TRUE or FALSE on hundreds o
pages. Any help is greatly appreciated
Thanks,
Ja

 

[fazlici]

I think I described my scenario wrong...excuse me, English is not my
first language. I'll try this again. Every member has a unique seven
digit number but some members have two.
For example:

James Smith 1003498
James J. Smith 1003498
John Cayer 4003849
John & Ann Cayer 4003849

In other words, I have a potential of multiple duplicate records. If I
use formula below, it looks for a record that is in A1 (assuming I
started my formula there) and that is not what I really need.

THank you for all your effort and help,

 

[Gord Dibben]

Jas

Perhaps you could filter for duplicates and copy them to another sheet or area
then delete them from the original list.

Debra Dalgleish has instructions for this.

http://www.contextures.on.ca/xladvfilter01.html#FilterUR

Gord Dibben Excel MVP