Results 1 to 8 of 8
  1. #1
    Glenn
    Guest

    Finding Duplicate Entries

    I have a work sheet with about 15,000 rows. I have a number of rows that I would like to get rid of that would bring the number down to around 9,000. Based on the contents of columns A,B,& D being identical, I would like to delete all but 1 instance of this row. Is there a way to do this automatically or would I need to write a scipt to do this? (Example - row 1, row 123 and row 3,040 have identical info in A,B & D - delete 2 of them and leave 1, but the sheet will contain other rows with identical info in these columns but not the same as the first ones, and so on...if that makes any sense.)
    I would appreciate any help with this.
    [img]/w3timages/icons/alien.gif[/img]

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Duplicate Entries

    Two quick methods come to mind:
    Sort your data by those three columns and do a manual inspection / deletion - OR - use the Advanced Filter (Data / Filter / Advanced Filter) to filter the list to unique values then copy this list to a new area.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Duplicate Entries

    Gene, a manual inspection of 15000 rows? Yuk!

    Do a sort on the three columns as you suggest, then (assuming row 1 contains headers) in cell E2 put the formula:
    =IF(AND(A2=A1,B2=B1,D2=D1),1,0). Next fill down to the end of the data, then replace the results with values only. The first occurence of duplicate entries now has a 0 in column E, the other copies have a 1. Sort on column E and delete the entries with 1 in column E.

  4. #4
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Duplicate Entries

    Yuk! -- I agree...
    That's why I like the 'Advanced Filter' approach. I've also used variations of your suggestion, but my brain was in 1/3 speed when I posted my response.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Duplicate Entries

    Yep I should have remembered that, duplicate entries came up in the old Lounge and Chip Pearson's site was recommended.

    Gosh is this new Lounge active, by the time I'd read through the newbies on offer there were two emails responding to my suggestion. Do you guys do anything but monitor the Lounge?

  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Location
    Los Angeles
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Duplicate Entries

    THIS IS BRILLIANT! Much better than advanced filter solutions, which is the way I've always taught it. So simple it hits you in the nose! THANK YOU.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Duplicate Entries

    Pleased you like it. Don't forget Legare's great tip that double clicking on the fill handle automatically fills down until there is a blank cell in the column to the left.

  8. #8
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Duplicate Entries

    Consider using these tricks:
    <A target="_blank" HREF=http://www.cpearson.com/excel/duplicat.htm#ExtractingUnique>http://www.cpearson.com/excel/duplic...tractingUnique</A>
    <center> Edited to create hyperlink</center>

Posting Permissions

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