0

I've merged two spreadsheets using the solution to Merge two excel files using a common column, but now I need to find the rows which were not merged because there was no matching value in the common column.

For example, I have two spreadsheets

Sheet 1

Name         email
John Doe     john@website
Jane Doe     jane@website
Greg Smith   greg@website

Sheet 2

email          status
john@website   active
greg@website   inactive
susan@website  inactive

Using the command =IFERROR(VLOOKUP(B1, 'Sheet 2'!A:B, 2, FALSE), 0) from Merge two excel files using a common column, I can get the following result

Sheet 1

Name         email          status
John Doe     john@website   active
Jane Doe     jane@website   0
Greg Smith   greg@website   inactive

But I would also like to make a third sheet, which has the rows of Sheet 2 which do not overlap with Sheet 1. So the desired output is

Sheet 3

email          status
susan@website  inactive

How can I do this? I've played around with other VLOOKUP commands, but I'm not sure how to tell VLOOKUP to find non-matching values.

Community
  • 1
2cents
  • 273

1 Answers1

1

It depends on how elegant / clean of a solution you need/"want"...

In [Sheet3], you can have a column & set it equal to the key-value column in [Sheet2]... then do a VLOOKUP on this [Sheet3][key-value column] for the table in [Sheet1], wrapping it in the IFERROR().

The cells that produce an error, don't exist in [Sheet1]... of course you'll have alot of "gaps" probably, but that can be fixed with a data sort if necessary.

PS BTW, this is begging for DB tables & INNER and OUTER JOINS; or if you need a pretty solution & don't mind some coding, you can try: https://blogs.msdn.microsoft.com/ericwhite/2008/11/20/inner-join-of-two-excel-tables/

George 2.0 Hope
  • 684