Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jan 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding Duplicates (Excel 2000 SR-1)

    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?

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    La Verne, California, USA
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Duplicates (Excel 2000 SR-1)

    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.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Duplicates (Excel 2000 SR-1)

    I would do something like this:


    <pre>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
    </pre>

    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •