Finding Duplicates (Excel 2000 SR-1)

Topic

New Reply

What is the best way to find duplicate records in an Excel worksheet? The worksheet has 7 columns and over 30,000 lines. There are two columns that need to be checked for dupes and are a combination of both numbers and letters of varying length. Any suggestions?

Reply | Quote

Replies

I personally would export the sheet to Access and there it becomes a table. Then a query can be built that has its criteria set to find duplicates, which can be done using one of the wizards. All that is necessary is to set the criteria under the field to >1 . You’ll get a recordset of duplicates.

Reply | Quote

Have a look at . I am sure that this will help. You can also find some other interesting things about duplicates on Chip Pearson’s site: Click Here. Then look for topics and scroll to ‘duplicates’.

Reply | Quote

I would do something like this:

Public Sub FindDups()
Dim I As Long, J As Long, lMax As Long
    imax = Range("A65536").End(xlUp).Row
    For I = 1 To imax - 1
        For J = I + 1 To imax
            If Cells(I, 1) = Cells(J, 1) Then
                MsgBox "Cells " & Cells(I, 1).Address & " and " & _
                  Cells(J, 1).Address & " are duplicates"
            End If
        Next J
    Next I
End Sub

Reply | Quote