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

bh24524

Active Member
Joined
Dec 11, 2008
Messages
256
Office Version
  1. 2013
  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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,532
There is a 2013 formula that works:

Book1
ABCDE
1Master ListList 2List 3List 3
2John Smith 1John Smith 2John Smith 1John Smith 1
3John Smith 2John Smith 5John Smith 3John Smith 3
4John Smith 3John Smith 4John Smith 4
5John Smith 4 
6John Smith 5 
7
Sheet13
Cell Formulas
RangeFormula
E2:E4E2=FILTER(A2:A6,COUNTIF(B2:B6,A2:A6)=0)
D2:D6D2=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$6)/ISERROR(MATCH(A$2:A$6,B$2:B$5,0))/(COUNTIF(D$1:D1,A$2:A$6)=0),1)),"")
Dynamic array formulas.


Column E has the Excel 365 formula to do it using the new array functions. It's closer to your Google Sheets formula.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,772
Office Version
  1. 365
Platform
  1. Windows
The 365 version does have a similar formula & there is a way of doing the same thing in 2013
+Fluff 1.xlsm
HIJK
1List 1List 23652013
2John Smith 1John Smith 2John Smith 1John Smith 1
3John Smith 2John Smith 5John Smith 3John Smith 3
4John Smith 3John Smith 4John Smith 4
5John Smith 4 
6John Smith 5 
7
Main
Cell Formulas
RangeFormula
J2:J4J2=FILTER(H2:H6,ISNA(MATCH(H2:H6,I2:I3,0)))
K2:K6K2=IFERROR(INDEX($H$2:$H$6,AGGREGATE(15,6,(ROW($H$2:$H$6)-ROW($H$2)+1)/(ISNA(MATCH($H$2:$H$6,$I$2:$I$3,0))),ROWS(K$2:K2))),"")
Dynamic array formulas.
 

bh24524

Active Member
Joined
Dec 11, 2008
Messages
256
Office Version
  1. 2013
  2. 2007
Thank you both for that! It's funny, as I was posting earlier I had "Aggregate" jump in my head for some reason. I just kinda dismissed it as "meh you don't know what you're talking about" kinda thing lol.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,532

ADVERTISEMENT

Glad we could help! :biggrin:

AGGREGATE is quite powerful, if you know how to use it. I see Fluff and I actually have slightly different approaches, but both use AGGREGATE. Anyway, let us know if you have any further questions.
 

bh24524

Active Member
Joined
Dec 11, 2008
Messages
256
Office Version
  1. 2013
  2. 2007
I would love to understand the syntax behind the entire formula. If either of you have time at some point, could you break it down perhaps piece by piece to explain what is doing what? I just love understanding/absorbing the syntax behind formulas if possible to be able to utilize them somehow in the future. You don't have to of course as does look like it would be pretty time consuming. Thank you either way. :)
 

bh24524

Active Member
Joined
Dec 11, 2008
Messages
256
Office Version
  1. 2013
  2. 2007

ADVERTISEMENT

Hmm I tried this in another spreadsheet I was using but something went wrong. I have to leave here for the day and cant yet explain, but I will post tomorrow what happened how I reproduced the formula above and what went wrong.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,772
Office Version
  1. 365
Platform
  1. Windows
With the formula I supplied the Match portion returns an array of #N/A & numbers based on whether a match was found
{#N/A;1;#N/A;#N/A;2}

The Isna then turns that into an array of true/false
{TRUE;FALSE;TRUE;TRUE;FALSE}

The row-row+1 part returns an array of numbers from 1 to however many rows you are using
{1;2;3;4;5}

that array of numbers is then divided by the true/false array where true equates to 1 & false to 0
{1;#DIV/0!;3;4;#DIV/0!}

The 15,6, tells the Aggregate function to return the smallest kth value & ignore any errors.

The Rows(K$2:K2) will return 1 for the first row it's in, then when dragged down will return 2 then 3 etc. And that is the kth value for the aggregate
So in row 2 it returns the smallest value (ignoring errors) from the {1;#DIV/0!;3;4;#DIV/0!} array & then in row 3 it will return the 2nd smalles value etc.
 

bh24524

Active Member
Joined
Dec 11, 2008
Messages
256
Office Version
  1. 2013
  2. 2007
Okay so like I said I tried this out on a separate spreadsheet I use and looking at my spreadsheet, I think I may have gotten my request backwards. I wish I could get xl2bb here at work but they have downloads forbidden so I have to work around it.

1620394448001.png


So basically in this scenario, The Bid History List is serving as my master list as it is an ongoing tracker of different jobs people have held as well as the dates those jobs began. The pasted list, my second set of data is what we take from a separate tab where we weekly enter any upcoming job changes. In this particular instance, I have entered new positions for employees right below John Smith 13's TRAINER bid in the second set of data. So John Smith1 is taking a new "Jan" position and therefore is not in the first set of data since he never held that position before. His LM number Name and job held should then appear in the Third set of data along with the names below his as they also never held those positions before.

If I can simply get the formula for the LM number, I can duplicate/tweak it ofc to get the name and job fields to appear in the third set of data. Your help is much appreciated!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,772
Office Version
  1. 365
Platform
  1. Windows
Can you copy/paste that sample to the board?
 

Forum statistics

Threads
1,143,909
Messages
5,721,456
Members
422,363
Latest member
Bogus_Potatoes

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
Top