Results 1 to 5 of 5

Thread: dupes

  1. #1


    is there any way to remove dups from an excell spreadsheet the field is only numbers ... thanks ...
    lets say we have data in 3 columns
    row1: bob 111111111 052573
    row2: robert111111111 052573
    row3: susy 222222222 111570
    row4: fred 333333333 122277
    row5: sam 444444444 010165

    i wanted to know any way you could remove the dup number in column 2 because it seems that bob and robert are the same person and just mis entered

    thanks in advance!!!

    this was originally posted in the virtualdr forum : <A target="_blank" HREF=></A>

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    South Carolina, USA
    Thanked 0 Times in 0 Posts

    Re: dupes

    First a couple of questions:

    1- Is the sheet sorted by the column or Columns that contain the duplicate values?

    2- Is it just one column you want to check for duplicates, or more than one (your example makes it look like more than one column may have duplicate values)?

    3- When a duplicate is found, which one do you want to delete?

    The following VBA code assumes that the sheet is sorted on the single column (Column [img]/forums/images/smilies/cool.gif[/img] containing duplicates, and that the one to be deleted is the one in the higher numbered row.

    <pre>Public Sub DeleteDups()
    Dim iLastRow As Integer, I As Integer
    iLastRow = Sheet1.UsedRange.Rows.Count
    For I = iLastRow To 2 Step -1
    If Cells(I, 2) = Cells(I - 1, 2) Then
    Cells(I, 2).EntireRow.Delete
    End If
    Next I
    End Sub

    Legare Coleman

  3. #3

    Re: dupes

    the sheet has 5 columns
    one column has a unique number (account number) ... if this column has two numbers that match (ie-same account number) i would like to remove all but the first entry. this includes all columns for that row so that the first record is saved. there could be anynumber of dupes (not restricted to just a duplicate but could contain many duplicates)

    im going to give you code a try


  4. #4
    Join Date
    Apr 2001
    Michigan, USA
    Thanked 0 Times in 0 Posts

    Re: dupes

    the easiest method may be to concatenate the second and third columns into a separate column (i.e. =B1&C1)and then search for dupes.

    When all the numbers are in one column, you could use conditional formatting (Format>Conditional Formatting...)to find the duplicates. the conditional format formula (assuming the data was in range D1500) would be


    (idea courtesy of John Walkenbach)

    Someone with a better grasp of array formulas may be able to suggest a way to combine the two processes.



  5. #5

    Re: dupes

    that vb module worked like a CHAMP!!!

    it saved me probabally 2 hours (considering there were over 11,000 records and i was at 6,000)

    I am indebted to you ... may i call you master

Posting Permissions

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