MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Duplicate Entries


Posted by Charles on September 14, 2000 4:13 AM

In my worksheet I have a sorted (ascending) list of numbers.
I need to find the duplicate entries.

I would like a Macro that would:
a) Let me pick the column to process
b) Process the column and stop at a duplicate entry.

Thank you

Charles


Posted by Charles on September 24, 2000 3:47 PM

Sorry this is a bit late (I've had tech problems) but:
THANK YOU!

Thanks again

Charles

Posted by Charles on September 24, 2000 3:48 PM


Sorry this is a bit late (I've had tech problems) but:
THANK YOU!
I now have a macro that identifies duplicates

Thanks again

Charles

Posted by Scott H on September 14, 0100 5:43 AM

Charles

Give this a try. You will need to change the end value in the For/Next loop based on how many rows your data contains (or just set it to a large number to cover any case).

Let me know how it works!

Sub FindDupes()
Dim varCol As String
varCol = Inputbox("Please enter the column number to search.", "Duplicates")
If Not (IsNumeric(varCol)) Then
MsgBox "Please enter in the column number only"
FindDupes
End If
For x = 1 To 1000
Set Sheet = ActiveSheet
If Sheet.Cells(x, varCol * 1).Value = Sheet.Cells(x + 1, varCol * 1).Value Then
MsgBox "Duplicate found at row " & x
Sheet.Cells(x, varCol * 1).Select
Exit Sub
End If
Next x
End Sub

Posted by Scott H on September 14, 0100 5:48 AM

Slight improvement - try this code instead (should avoid any recursive errors)

Sub FindDupes()
Dim varCol As String
SelectColumn:
varCol = Inputbox("Please enter the column number to search.", "Duplicates")
If Not (IsNumeric(varCol)) Then
MsgBox "Please enter in the column number only"
GoTo SelectColumn
End If
For x = 1 To 1000
Set Sheet = ActiveSheet
If Sheet.Cells(x, varCol * 1).Value = Sheet.Cells(x + 1, varCol * 1).Value Then
MsgBox "Duplicate found at row " & x
Sheet.Cells(x, varCol * 1).Select
Exit Sub
End If
Next x
End Sub