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

**tkalinoski** · 10-07-2021, 04:45 PM

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

**alansidman** · 10-07-2021, 04:47 PM

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

**tkalinoski** · 10-07-2021, 05:15 PM

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

**Haluk** · 10-08-2021, 04:26 AM

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

=UNIQUE(FILTER(Submissions!B2:E,Submissions!E2:E="abc", Submissions!C2:C<>Excluded!B2:B, Submissions!D2:D<>Excluded!C2:C))

I've entered this formula in cell F2 of your sheet "Results - Should Be"

**tkalinoski** · 10-08-2021, 11:33 AM

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"

**AliGW** · 10-08-2021, 04:29 AM

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.

**Haluk** · 10-08-2021, 12:00 PM

I will have a look ....

**tkalinoski** · 10-08-2021, 12:23 PM

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.

**Haluk** · 10-08-2021, 12:59 PM

Try this and see if it works...

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

=UNIQUE(QUERY({Submissions!B2:E,Excluded!A2:C},"Select Col1, Col2, Col3 Where Col4='abc' And Col2<>Col6"))

*************************************************************************************************

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;

=A2 & B2

and in sheet "Submissions", the KEY formula in cell F2 is;

=B2 & C2

Drag down the cells to fill the necessary range ...

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

=QUERY(UNIQUE(FILTER(Submissions!B2:F,Submissions!E2:E="abc", Submissions!C2:C<>Excluded!B2:B, Submissions!F2:F<>Excluded!D2:D)),"Select Col1, Col2, Col3")

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Compare Two Columns in Two Sheets to Remove Duplicates and Return One Copy Only

Macro to Compare two columns and publish sheet name in case columns dont match

[SOLVED] Compare, Match, and sort multi columns

Excel to compare several sheets against main one and return duplicates on new sheet

Compare multi Columns in Different Sheets and Post it result on a 3rd sheet

Compare two columns and return list of duplicates

[SOLVED] using array, Match, trying to eliminate duplicates -> how to return or translate result to a boolean?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions