Duplicate Records

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 suggested by Jim it looks for a record that is in A1
(assuming I started my formula there) and that is not what I really
need.
What I need is a formula that will check the records in a column,
compare them, determine if there is a duplicate and mark it or flag it
somehow.

THank you for all your effort and help,
Jas

Original Message

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

Try this macro. I had recently used it to find duplicate
records in a column. When it find the duplicates, it
highlights them. I am including the link to the article
that includes this marcro.

http://support.microsoft.com/default.aspx?scid=kb;en-
us;213355&Product=xlw2K

Sub FindDups ()
'
' NOTE: You must select the first cell in the column
and
' make sure that the column is sorted before running
this macro
'
ScreenUpdating = False
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
Do While ActiveCell ""
If FirstItem = SecondItem Then
ActiveCell.Offset(Offsetcount,0).Interior.Color =
RGB(255,0,0)
Offsetcount = Offsetcount + 1
SecondItem = ActiveCell.Offset(Offsetcount,
0).Value
Else
ActiveCell.Offset(Offsetcount, 0).Select
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1,0).Value
Offsetcount = 1
End If
Loop
ScreenUpdating = True
End Sub

Hope it works.
-----Original Message-----
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 suggested by Jim it looks for a record that
is in A1
(assuming I started my formula there) and that is not
what I really
need.
What I need is a formula that will check the records in
a column,
compare them, determine if there is a duplicate and mark
it or flag it
somehow.

THank you for all your effort and help,
Jas

Original Message

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
.

Jas:

Use the COUNTIF function, and modify the range specification depending upon
your ultimate goal.

I'm assuming the member number (a unique identifier, as I understand) is in
column B and the first record is in row 1. If you would like to eliminate or
filter all but the first occurrance of a member's number, use
=COUNTIF(B$1:B1,B1) in the first row and copy the formula to all rows. Then,
you can eliminate or filter any row where the result 1.

If you want to know how many occurrances there are of each member's number,
then (assuming the last row is row 9999) use =COUNTIF(B$1:B$9999,B1) in the
first row and copy the formula down all rows. You could also use this to
filter and show all occurrances of a duplicated member number (filter on the
member number and result1)

Phil
"Jas" wrote in message
om...
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 suggested by Jim it looks for a record that is in A1
(assuming I started my formula there) and that is not what I really
need.
What I need is a formula that will check the records in a column,
compare them, determine if there is a duplicate and mark it or flag it
somehow.

THank you for all your effort and help,
Jas

Original Message

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

Jas,

I'm having the same problem as you. Have you come up with
a solution?
-----Original Message-----
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 suggested by Jim it looks for a record that
is in A1
(assuming I started my formula there) and that is not
what I really
need.
What I need is a formula that will check the records in a
column,
compare them, determine if there is a duplicate and mark
it or flag it
somehow.

THank you for all your effort and help,
Jas

Original Message

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
.

kis and Jas,

I'm not sure if this has been answered or not - one reason why you
should keep posts in the same thread actually in the same thread - but
here is one way to check for duplicates.

To check for duplicates in column B, insert a new column C. Enter this
formula in cell C1:

=COUNTIF(B:B, B1)1

Copy C1 down to C2:CXXXX, and sort the entire table based on column C
first and column B second. The values with TRUE in column C are
duplicates, and the duplicates should be in adjacent cells in column
B.

HTH,
Bernie
MS Excel MVP

"kis" wrote in message
...
Jas,

I'm having the same problem as you. Have you come up with
a solution?
-----Original Message-----
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 suggested by Jim it looks for a record that
is in A1
(assuming I started my formula there) and that is not
what I really
need.
What I need is a formula that will check the records in a
column,
compare them, determine if there is a duplicate and mark
it or flag it
somehow.

THank you for all your effort and help,
Jas

Original Message

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
.