Duplicates

K

Krissie C

Greetings,
I'm quite new at Excel, and computers in general, so please bear w/me. I'm
working w/Excel 2000. Anyway, I'm trying to find a formula (or create a
macro) for duplicates. I've seen a bunch of helpful posts and links on the
subject , but none that exactly solve my problem. I need to pull both the
duplicate and its matching original up to the top of a list for further
review/comparison, and do not want to delete the duplicate. I'm looking at
serial numbers in a worksheet w/ over 15,000 rows and growing. For example:

D
h24479 I'd like both 775frz to move to the top
of the list
B271166
775frz
kl20098
775frz

I was so proud of myself when I thought I'd created my first successful
macro(using conditional formatting, then sorting), but my boss pointed out I
was only pulling the duplicates to the top.
Thanks in advance to anyone willing to help,
Krissie
 
B

Bob Phillips

Hi Krissie,

Try this

Sub ShowDuplicates()
Dim iLastrow As Long
Dim i As Long
Dim j As Long

iLastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastrow
If Application.CountIf(Range("A:A"), Cells(i, "A").Value) > 1 Then
j = j + 1
Cells(j, "B").Value = Cells(i, "A").Value
End If
Next i
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
K

Krissie C

Thank you both so much! It will probably be a few days before I have time to
try out these suggestions, but I'll let you know how it goes.
Peace,
Krissie
 
Top