Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Nov 2012
    Posts
    14
    Thanks
    2
    Thanked 1 Time in 1 Post

    excel 2007 delete duplicate doesn't match function results

    I have been working with a database to find duplicate names which are a result of two separate service areas sometimes working with the same person. For reporting purposed, I want to only count everyone once.
    I copied the data [First name, Last name] into a separate sheet to avoid contaminating the original. The data was sorted.
    I went into data and chose remove duplicates. I confirmed that I wanted it to check both first and last name as a match before deleting. I got my result.
    Then as a cross check I went back to the original data and wrote a formula.
    A B C
    First Last
    1
    2

    I put the formula in C and copied it down for the remaining entries. I then summed.
    The answer was different by 28 entries.
    My formula was this =if(A1=A2,if(B1=B2,0,1),1) If any 2 adjacent rows in column A are the same then check if the same situation exists in column B. If true assign a 0, otherwise assign a 1. I then summed all the 1's [non duplicates]
    The delete duplicates method found 1084 unique records. The formula found 1112. I checked manually that all of the zero results were found only once in the first process. I couldn't see any dupes using the delete duplicates.
    I'm baffled. Anyone have thoughts?
    Sorry I can include the original file for privacy reasons.
    thanks
    Glenn

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Glenn

    So, if your formula method found 28 more entries, you need to find where and what those 28 entries are.
    So I would add a new column and a formula to join the first and last names together in both sets of data (i.e. the sheet which has the 1112 entries and the sheet which has the 1084 entries).

    And then, in a new column on the sheet with the 1112 entries, use an INDEX(MATCH) formula to retrieve the row number of the corresponding entry found on the sheet with 1084 entries.

    The idea is that you should have 28 #N/A values which will identify these records.

    zeddy

  3. The Following User Says Thank You to zeddy For This Useful Post:

    glenn questions (2013-05-16)

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Glenn,

    The question still remains, "Why didn't your formula come up with the same number of unique names?" One cause may be in the sorting process. If you sorted by column A (first name) only, and not by both, you will receive different values using your formula.

    In the image below, there are three groupings containing a mixture of first and last names with the duplicates being color coded. Grouping 1 was sorted by first name only and group 2 was sorted by first and last names. Unique names were calculated using your formula for both of these groups then compared to the unique names using the "Remove Duplicates" function in Group 3. You will notice in group 1 that when an exact match is not grouped together, the duplicate values are not discarded. Conversely, when sorted with both first and last names, all the duplicate values were discarded.

    Groupings.jpg

    Could this have been the cause?

    Maud

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    glenn questions (2013-06-21)

  6. #4
    New Lounger
    Join Date
    Nov 2012
    Posts
    14
    Thanks
    2
    Thanked 1 Time in 1 Post
    Hi Maud, I thanked zeddy for the solution but I replied with exactly your question. "Why didn't they match?" I notice in my description I don't say how I sorted and frankly it is long enough ago now that I don't remember. Your answer is the only answer so far and I like it as I hate mysteries in excel. When you don't know why errors exist it is hard to avoid them. Thanks for your curiosity.
    Glenn

Tags for this Thread

Posting Permissions

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