Return only unique occurrences of data from two tables

Question

Level 1

4 points

Return only unique occurrences of data from two tables

Hi there,

Example:

TABLE 1

ID	Name	Size
CK101	Coke	12oz
SP101	Sprite	8oz
PS101	Pespi	12oz
MD101	Mountain Dew	16oz

TABLE 2

ID	Name	Size
CK101	Coca Cola	8oz
IT101	Iced Tea	12oz
PS101	Pepsi Cola	16oz
DP101	Dr. Pepper	16oz

I have two tables in which some of the values in the ID column are the same, but not all. I want to create a 3rd table in which it returns the values of only the IDs in table 1 that do not have a match in table 2. I feel like I've gotten pretty close trying a mix of IF, IFERROR, and VLOOKUP, mixed with a filter to hide blanks, but I can't seem to figure it out.

Example of TABLE 3

ID
SP101
MD101

NOTE: I don't need table 3 to return the differences from columns B or C, just the ID from column A.

This is not a necessity, but it would be preferable to only display each ID one time, as I will have some that have multiple occurrences.

Thanks so much in advance for your help!

Jesse

Posted on Mar 2, 2018 5:27 AM

Reply

Answer 1

SGIII

Level 8

35,306 points

Mar 2, 2018 6:45 AM in response to moun10addict

A simple way to identify the ones without a match in Table 2 would be like this:

Add a column with this formula in D2, filled down:

=IFERROR(MATCH($A2,Table 2::A,0),"No Match")

Then filter on that column for No Match.

And copy paste the IDs to wherever you need them:

SG

Reply

Answer 2

Badunit

Level 7

25,976 points

Mar 2, 2018 7:15 AM in response to moun10addict

With SGIII's formula in column D of Table 1, you can use the following formula to populate the non-matching IDs in Table 3:

=IFERROR(INDEX(Table 1::A,SMALL(Table 1::$D,ROW()−1),1),"")

If you don't like the blue triangles that will result from this, in SGIII's formula change "no match" to a large number like 999999 (deleting the quotes too).

Reply

Answer 3

moun10addict Author

Level 1

4 points

Mar 2, 2018 7:29 AM in response to SGIII

Fantastic! That worked great. I modified it a bit to better fit what I'm doing. Instead of creating an extra column in table 1 I went straight into table 3 and did:

=IFERROR(IF(MATCH(Table 1::$A2,'Table 1-1'::A,0),"match found",Table 1::$A2),Table 1::$A2)

Which gives me:

ID
match found
SP101
match found
MD101

Then I filtered out "match found" and was left with only the non-matching IDs. Now I can do VLOOKUP and fill out the rest of the table with the info I need from the other columns.

Thanks very much for your help! I'm fairly new with some of these spreadsheet formulas and wasn't familiar with MATCH yet, which is exactly what I needed.

Have a great day.

Reply

Answer 4

Badunit

Level 7

25,976 points

Mar 2, 2018 7:45 AM in response to Badunit

Oops, sorry, when I was playing with this I had column D show row numbers when there was no match and 99999 when there was match. The formula I provided then collected the data for the rows that had row numbers (ie., the non-matching IDs).

Reply