+ Reply to Thread
Results 1 to 9 of 9

Compare multi columns in 2 sheets and return records that dont match, eliminate duplicates

  1. #1
    Registered User
    Join Date
    04-26-2017
    Location
    United States
    MS-Off Ver
    2010
    Posts
    6

    Post Compare multi columns in 2 sheets and return records that dont match, eliminate duplicates

    The Excluded sheet contains the dob, last name, and first name of people that should be excluded from the results

    The submissions sheet contains the dob, last name and first name of people that should and should not be included in the results. The sheet also contains a submission date and code column.

    The results sheet should contain the dob, last name, and first name of all people on the submissions sheet, that are not on the excluded sheet. When making the comparison, I only want to compare last name and date of birth.

    I was able to get that working, however, I am ending up with some duplicates because there are legitimate duplicate rows in the submissions sheet that have the same last name and date of birth. I do not want to include the duplicate records in the results sheet.

    Furthermore, I only want to include records from the submissions sheet that have a value of “abc” in the code field.

    I have a sample spreadsheet, but this forum will not allow me to post the link

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,484

    Re: Compare multi columns in 2 sheets and return records that dont match, eliminate duplic

    Read the yellow banner at the top of this page to learn how to post your file.
    Alan


    Have you debugged your code?
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    04-26-2017
    Location
    United States
    MS-Off Ver
    2010
    Posts
    6

    Re: Compare multi columns in 2 sheets and return records that dont match, eliminate duplic

    attached is a text file with the link to my google sheet.
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit on Win7
    Posts
    945

    Re: Compare multi columns in 2 sheets and return records that dont match, eliminate duplic

    I'm not sure but, check this formula and see if it is what you need;

    Please Login or Register  to view this content.
    I've entered this formula in cell F2 of your sheet "Results - Should Be"
    Last edited by Haluk; 10-08-2021 at 04:45 AM.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,519

    Re: Compare multi columns in 2 sheets and return records that dont match, eliminate duplic

    So is this a Google Sheets query? If so, I’ll move the thread. If not, please post an Excel workbook using the attachment feature as described in the top yellow banner.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  6. #6
    Registered User
    Join Date
    04-26-2017
    Location
    United States
    MS-Off Ver
    2010
    Posts
    6

    Re: Compare multi columns in 2 sheets and return records that dont match, eliminate duplic

    This doesnt seem to work. Note I changed the excluded record from John Doe to John Does. I would expect John Doe to show up in your formula in cell F2 of your sheet "Results - Should Be"

  7. #7
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit on Win7
    Posts
    945

    Re: Compare multi columns in 2 sheets and return records that dont match, eliminate duplic

    I will have a look ....

  8. #8
    Registered User
    Join Date
    04-26-2017
    Location
    United States
    MS-Off Ver
    2010
    Posts
    6

    Re: Compare multi columns in 2 sheets and return records that dont match, eliminate duplic

    I received your message...

    According to the list in sheet "Excluded", the data "John Does" is excluded from the record set listed in sheet "Results - Should Be" and the only satisfied record is "Jane Smith".

    So, what is your expected result(s) if my suggestion doesn't work for you?

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    The results sheet should contain the dob, last name, and first name of all people on the submissions sheet, that are not on the excluded sheet and have a value of “abc” in the code field. When making the comparison, I only want to compare last name and date of birth.

    The result should yield

    1/1/2000 Doe John
    1/2/2000 Smith Jane

    As those records have a code of "abc" and netiher of those last name dob combinations are in the excluded sheet.

  9. #9
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit on Win7
    Posts
    945

    Re: Compare multi columns in 2 sheets and return records that dont match, eliminate duplic

    Try this and see if it works...

    Note: It's not a complete solution as it only checks "Last Name" from the sheet "Excluded"

    Please Login or Register  to view this content.
    *************************************************************************************************

    In order to have a complete solution, you need a unique ID for the data in sheets "Exclude" and "Submissions".

    So, if you prefer this approach you will need to add a column in both of these sheets labeled as "KEY" in the top of the added columns.

    In sheet "Exclude", the KEY formula in cell D2 is;

    Please Login or Register  to view this content.
    and in sheet "Submissions", the KEY formula in cell F2 is;

    Please Login or Register  to view this content.
    Drag down the cells to fill the necessary range ...

    Then, use this formula in sheet "Results To Be" to get the expanded list;

    Please Login or Register  to view this content.
    Attached Images Attached Images
    Last edited by Haluk; 10-08-2021 at 01:57 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Compare Two Columns in Two Sheets to Remove Duplicates and Return One Copy Only
    By major_johnson in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-30-2021, 01:59 PM
  2. Macro to Compare two columns and publish sheet name in case columns dont match
    By adaws in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-06-2014, 01:21 PM
  3. [SOLVED] Compare, Match, and sort multi columns
    By BeyondInvisible in forum Excel General
    Replies: 8
    Last Post: 12-13-2012, 08:12 PM
  4. Replies: 2
    Last Post: 11-13-2012, 02:56 PM
  5. Compare multi Columns in Different Sheets and Post it result on a 3rd sheet
    By steven.demei in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-19-2011, 07:18 AM
  6. Compare two columns and return list of duplicates
    By asha3010 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-15-2010, 06:44 AM
  7. Replies: 5
    Last Post: 04-04-2005, 05:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1