+ Reply to Thread
Results 1 to 3 of 3

how to do multi column comparison and display matches and differences with google sheets

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

    how to do multi column comparison and display matches and differences with google sheets

    I need help performing a multi-column comparison. the "Registrants" sheets contains a fictitious list of people that registered for an event. The "Employee List" sheet contains a list of employees exported from an information system. I'd like the third sheet, "Validated" to list the first name, last name, and date of birth, of all rows in "registrants" that are also present in "employee list." When performing the comparison, only last name and date of birth should be compared.The fourth sheet, "Additional Validation needed - no match" should contain all rows within "registrants" that do not have a last name and date of birth match in "employee list". I've tried multiple formulas but cant figure this out. your help is appreciated.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,133

    Re: how to do multi column comparison and display matches and differences with google shee

    Try this in cell A2 of sheet "Validated";

    Please Login or Register  to view this content.
    and this, in cell A2 of sheet "Additional Validation needed - no match";

    Please Login or Register  to view this content.

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

    Re: how to do multi column comparison and display matches and differences with google shee

    something else that works without a join is
    =filter( 'Data Set 1'!A2:C, match('Data Set 1'!A2:A & 'Data Set 1'!C2:C, 'Data Set 2'!A2:A & 'Data Set 2'!C2:C, 0) )

    to do the opposite, wrap the match() in a isna().

+ 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. Display differences between 2 sheets
    By Mindaugas S. in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-07-2020, 10:19 AM
  2. Google Sheets: Formula I can use with multi conditions
    By CullyM in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 3
    Last Post: 07-12-2018, 01:06 PM
  3. Copy Multi Rows From Multi Sheets Based On Column Value And Create New Workbooks
    By Huskersippi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-24-2017, 04:51 PM
  4. Comparison of 2 sheets from different workbooks and highlighting the differences
    By zsimur in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-31-2013, 07:31 AM
  5. Replies: 1
    Last Post: 04-23-2013, 12:46 AM
  6. Replies: 5
    Last Post: 11-30-2012, 01:33 PM
  7. multi-column comparison and lookup
    By 7starmantis in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-21-2009, 11:55 AM

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