Compare two lists and generate a new list of items missing from one list

bh24524

Active Member
Joined
Dec 11, 2008
Messages
365
Office Version
  1. 365
  2. 2007
Hello, so I learned how to do this in Google Sheets the other day. It was as the title says a comparison between two lists that I wanted a formula to list all the items from the first list that were NOT contained on the second list. I know you can do this a different way utilizing conditional formatting, but I really liked how the Google Sheets did it and wondered if maybe Excel 2013 had some kind of formula equivalent that could do it.

Ideally this is what I'm after just with peoples names as an example:

List 1:(aka Master List - Contained on the Building Static tab hence its name in the formula)
John Smith 1
John Smith 2
John Smith 3
John Smith 4
John Smith 5

List 2: (Name list contained on the current tab where the formula is)
John Smith 2
John Smith 5

List 3: (The list that is auto-generated by the formula)
John Smith 1
John Smith 3
John Smith 4

Those names in list 3 again are those names that did NOT appear in list 2.

The formula that I used in Google sheets just for reference was:

Excel Formula:
=arrayformula(filter('Building Static'!D3:F522,iserror(match('Building Static'!D3:D522,B21:B91,0))))

In google sheets this worked perfectly and I had never used this formula before but I got to think surely I could use this type of formula in some of the excel sheets I use at work.

I had tried using this formula, the arrayformula function but Excel said it was not a valid function so I figured maybe 2013 didn't have that function. I thought I read somewhere that a later version of Excel DOES have it but can't remember for certain.

Just wondered if there is any type of Excel 2013 formula that can actually do this. If someone might advise? Please and thank you.
 
Hi & welcome to MrExcel.
You're lists do not have any values in common.
You will need to start a new thread explaining exactly what you are trying to do.
 
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top