Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    duplicate records (excel 2000)

    I have a spreadsheet that has a column with numbers - say 12345. There are duplicate numbers in this column. I am manually deleting the duplicate rows for the duplicate numbers. Is there any way to automatically delete records that have duplicates in this column?

  2. #2
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: duplicate records (excel 2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29>

    Short of writing a macro, I haven't found an easy way to do this.

    Anyone know of an easy way to do this?

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: duplicate records (excel 2000)

    Off the top of my head you have at least 2 ways.

    1. Assume data starts in A1. Sort by column A. In B2 put the formula =IF(A2=A1,"DUP",""). Copy this down as far as required & then paste value the results in place. You can then sort by column B and eliminate the duplicates.

    2. Data - Filter - Advanced Filter. It should be easy enough to follow from there

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: duplicate records (excel 2000)

    1)Advanced filter - Unique records

    another way:
    2)Add a column (I assumed you are looking in COl A for Dups and you start in A2) and copy this.

    =ISERROR(MATCH(A2,$A$1:A1,0))

    This will be TRUE for unique values and False for dups. Autofilter on False and delete the rows

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: duplicate records (excel 2000)

    This spreadsheet has multiple columns and when I get rid the the duplicate numbers that are the in the group number column, I am importing this into an access database. The spreadsheet has over 5000 records so if I could delete these records without having to do it manually, it would save a lot of time. I tried to use the advanced filter but I wasn't having any luck. I highlighted the whole spreadsheet and then for the criteria, I used the column that had the duplicates. I checked Unique records only. It got rid of everything. I wasn't sure if this did work, I could import what was filtered to access or would the whole spreadsheet import.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: duplicate records (excel 2000)

    Thank you - that worked perfectly.

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

    Re: duplicate records (excel 2000)

    If you have to do this often, the code in This Post might make it easier.
    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
  •