Duplicate Records

[Jas]

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

 

[Tami]

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.

 

[Phil Caskey]

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 result>1)

Phil

 

[kis]

Jas,

I'm having the same problem as you. Have you come up with
a solution?

 

[Bernie Deitrick]

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