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

[bh24524]

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 1aka 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:

=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.

 

[Eric W]

There is a 2013 formula that works:

Book1
	A	B	C	D	E
1	Master List	List 2		List 3	List 3
2	John Smith 1	John Smith 2		John Smith 1	John Smith 1
3	John Smith 2	John Smith 5		John Smith 3	John Smith 3
4	John Smith 3			John Smith 4	John Smith 4
5	John Smith 4
6	John Smith 5
7
Sheet13

Cell Formulas
Range	Formula
E2:E4	E2	=FILTER(A2:A6,COUNTIF(B2:B6,A2:A6)=0)
D2:D6	D2	=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]

The 365 version does have a similar formula & there is a way of doing the same thing in 2013

+Fluff 1.xlsm
	H	I	J	K
1	List 1	List 2	365	2013
2	John Smith 1	John Smith 2	John Smith 1	John Smith 1
3	John Smith 2	John Smith 5	John Smith 3	John Smith 3
4	John Smith 3		John Smith 4	John Smith 4
5	John Smith 4
6	John Smith 5
7
Main

Cell Formulas
Range	Formula
J2:J4	J2	=FILTER(H2:H6,ISNA(MATCH(H2:H6,I2:I3,0)))
K2:K6	K2	=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]

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]

Glad we could help!

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]

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]

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]

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]

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.

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]

Can you copy/paste that sample to the board?