Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    duplicate records (xp & 2003)

    Is there a way to pull out or identifying duplicate entries in different records of an Excel data base?

  2. #2
    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 (xp & 2003)

    You can use data - filter - advanced filter to extract out the unique records in a database...

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: duplicate records (xp & 2003)

    I need to get statistics as to how many duplicates by a name and for each name duplicate dates. Is that possible? if so how does advance filter work?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: duplicate records (xp & 2003)

    If you want an automatic count of occurrences of unique items, a pivot table might be more suitable.
    What exactly do you mean by "for each name duplicate dates"?

  5. #5
    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 (xp & 2003)

    1) To get a count of the duplicates you can use a Pivot table report.

    In the row field put in each of the fields and then add one to the data field as a COUNT. You can delete/hide all the row totals and just look at the column Total. Any column with total of more than 1 has a duplicate and the duplicate count is the total -1. You could add a column to the right of the pivot with a formula to subtract 1 from the total, then filter on this new column for not equal to zero and will be a list of the duplicates.

    2) Data - filter - advanced filter
    Select the option: " Copy to another location"
    Fill in the range of the datatable in the "List range"
    In the "Copy to:" select ther range to copy the records to
    Check the "Unique records only"
    [ok]

    In the copy to location, you will have list of the unique records.
    Steve

  6. #6
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: duplicate records (xp & 2003)

    I need to find all duplicate(or moreof the same) entries by the ss # and also if the duplicate(or more of the same) dates of service.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: duplicate records (xp & 2003)

    Could you post a small sample workbook?

  8. #8
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: duplicate records (xp & 2003)

    I included a file with duplicates highlighted in different colors. As you will see that once I can identify the duplicates only from the rest of the data, I can analyze the duplicate as to date of service, site, name, etc.

    so the question is how can i pull out duplicates by name and by id#.

    thanks


    Martin
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: duplicate records (xp & 2003)

    In the attached version, I have added two columns with COUNTIF formulas that count the number of instances of a name and of an ID#.
    Attached Files Attached Files

Posting Permissions

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