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.

 

[bh24524]

	Bid History List					Pasted List from New Bid Tab				Names contained in Pasted List NOT on history list
LM NO.	EMPLOYEE	JOB HELD	DATE		LM NO.	EMPLOYEE	JOB HELD		LM NO.	EMPLOYEE	JOB HELD
154​	John Smith 1	GW	8/16/2020​		154​	John Smith 1	GW
410​	John Smith 2	GW	3/28/2021​		410​	John Smith 2	GW
565​	John Smith 3	GW	1/12/2020​		565​	John Smith 3	GW
963​	John Smith 4	GW	1/12/2020​		963​	John Smith 4	GW
944​	John Smith 5	GW	1/12/2020​		944​	John Smith 5	GW
542​	John Smith 6	GW	1/12/2020​		542​	John Smith 6	GW
247​	John Smith 7	GW	10/18/2020​		247​	John Smith 7	GW
422​	John Smith 8	JAN	1/12/2020​		422​	John Smith 8	JAN
71​	John Smith 9	GW	2/14/2021​		71​	John Smith 9	GW
980​	John Smith 10	GW	1/12/2020​		980​	John Smith 10	GW
187​	John Smith 11	GW	10/4/2020​		187​	John Smith 11	GW
336​	John Smith 12	GW	1/12/2020​		336​	John Smith 12	GW
337​	John Smith 13	TRAINER	1/12/2020​		337​	John Smith 13	TRAINER
337​	John Smith 13	GW	2/9/2020​		154​	John Smith 1	JAN
337​	John Smith 13	LIFT	1/10/2021​		542​	John Smith 6	FR JAN
415​	John Smith 14	LIFT	1/12/2020​		422​	John Smith 8	BALER
312​	John Smith 15	GW	1/12/2020​		337​	John Smith 13	DMG RCP
312​	John Smith 15	FR LIFT	5/10/2020​		980​	John Smith 10	FR DMG RCP

Does that help?

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1		Bid History List					Pasted List from New Bid Tab				Names contained in Pasted List NOT on history list
2	LM NO.	EMPLOYEE	JOB HELD	DATE		LM NO.	EMPLOYEE	JOB HELD		LM NO.	EMPLOYEE	JOB HELD
3	154	John Smith 1	GW	16/08/2020		154	John Smith 1	GW		154	John Smith 1	JAN
4	410	John Smith 2	GW	28/03/2021		410	John Smith 2	GW		542	John Smith 6	FR JAN
5	565	John Smith 3	GW	12/01/2020		565	John Smith 3	GW		422	John Smith 8	BALER
6	963	John Smith 4	GW	12/01/2020		963	John Smith 4	GW		337	John Smith 13	DMG RCP
7	944	John Smith 5	GW	12/01/2020		944	John Smith 5	GW		980	John Smith 10	FR DMG RCP
8	542	John Smith 6	GW	12/01/2020		542	John Smith 6	GW
9	247	John Smith 7	GW	18/10/2020		247	John Smith 7	GW
10	422	John Smith 8	JAN	12/01/2020		422	John Smith 8	JAN
11	71	John Smith 9	GW	14/02/2021		71	John Smith 9	GW
12	980	John Smith 10	GW	12/01/2020		980	John Smith 10	GW
13	187	John Smith 11	GW	04/10/2020		187	John Smith 11	GW
14	336	John Smith 12	GW	12/01/2020		336	John Smith 12	GW
15	337	John Smith 13	TRAINER	12/01/2020		337	John Smith 13	TRAINER
16	337	John Smith 13	GW	09/02/2020		154	John Smith 1	JAN
17	337	John Smith 13	LIFT	10/01/2021		542	John Smith 6	FR JAN
18	415	John Smith 14	LIFT	12/01/2020		422	John Smith 8	BALER
19	312	John Smith 15	GW	12/01/2020		337	John Smith 13	DMG RCP
20	312	John Smith 15	FR LIFT	10/05/2020		980	John Smith 10	FR DMG RCP
21
Data

Cell Formulas
Range	Formula
J3:L9	J3	=IFERROR(INDEX(F$3:F$20,AGGREGATE(15,6,(ROW($G$3:$G$20)-ROW($G$3)+1)/(ISNA(MATCH($B$3:$B$20&"|"&$C$3:$C$20,$G$3:$G$20&"|"&$H$3:$H$20,0))),ROWS(J$3:J3))),"")

This may need array entry for 2013.

 

[bh24524]

I can see that it is working on this small data sample but its not working on the bigger live data sample. I played around with it and made the 1st data set bigger just merely duplicating what was there. in the second set of data, I pasted some names from the first and pasted some different jobs. I then sorted that second row just to have it more organized and it looks like this:

I ofc adjusted the formula to include the additional rows but as you see, it has names now in the third column that shouldn't be there. You can see the formula in my screenshot but here is also the data again if needed:

	Bid History List					Pasted List from New Bid Tab				Names contained in Pasted List NOT on history list
LM NO.	EMPLOYEE	JOB HELD	DATE		LM NO.	EMPLOYEE	JOB HELD		LM NO.	EMPLOYEE	JOB HELD
154​	John Smith 1	GW	8/16/2020​		154​	John Smith 1	GW		154​	John Smith 1	GW
410​	John Smith 2	GW	3/28/2021​		154​	John Smith 1	GW		187​	John Smith 11	DMG RCP
565​	John Smith 3	GW	1/12/2020​		154​	John Smith 1	GW		337​	John Smith 13	GW
963​	John Smith 4	GW	1/12/2020​		980​	John Smith 10	GW		565​	John Smith 3	JAN
944​	John Smith 5	GW	1/12/2020​		980​	John Smith 10	FR DMG RCP		963​	John Smith 4	GW
542​	John Smith 6	GW	1/12/2020​		980​	John Smith 10	GW		944​	John Smith 5	GW
247​	John Smith 7	GW	10/18/2020​		187​	John Smith 11	GW		542​	John Smith 6	GW
422​	John Smith 8	JAN	1/12/2020​		187​	John Smith 11	DMG RCP		247​	John Smith 7	GW
71​	John Smith 9	GW	2/14/2021​		336​	John Smith 12	GW		71​	John Smith 9	JAN
980​	John Smith 10	GW	1/12/2020​		336​	John Smith 12	FR DMG RCP		0​	0​	0​
187​	John Smith 11	GW	10/4/2020​		337​	John Smith 13	GW		0​	0​	0​
336​	John Smith 12	GW	1/12/2020​		337​	John Smith 13	DMG RCP		0​	0​	0​
337​	John Smith 13	TRAINER	1/12/2020​		337​	John Smith 13	GW		0​	0​	0​
337​	John Smith 13	GW	2/9/2020​		410​	John Smith 2	GW		0​	0​	0​
337​	John Smith 13	LIFT	1/10/2021​		565​	John Smith 3	JAN
415​	John Smith 14	LIFT	1/12/2020​		963​	John Smith 4	GW
312​	John Smith 15	GW	1/12/2020​		944​	John Smith 5	GW
312​	John Smith 15	FR LIFT	5/10/2020​		542​	John Smith 6	GW
154​	John Smith 1	GW	1/12/2020​		542​	John Smith 6	GW
410​	John Smith 2	GW	1/12/2020​		542​	John Smith 6	JAN
565​	John Smith 3	GW	1/12/2020​		247​	John Smith 7	GW
963​	John Smith 4	GW	1/12/2020​		247​	John Smith 7	GW
944​	John Smith 5	GW	1/12/2020​		422​	John Smith 8	GW
542​	John Smith 6	GW	1/12/2020​		422​	John Smith 8	GW
247​	John Smith 7	GW	1/12/2020​		422​	John Smith 8	GW
422​	John Smith 8	JAN	1/12/2020​		71​	John Smith 9	JAN
71​	John Smith 9	GW	1/12/2020​		71​	John Smith 9	GW
980​	John Smith 10	GW	1/12/2020​
187​	John Smith 11	GW	1/12/2020​
336​	John Smith 12	GW	1/12/2020​
337​	John Smith 13	TRAINER	1/12/2020​
337​	John Smith 13	GW	1/12/2020​
337​	John Smith 13	LIFT	1/12/2020​
415​	John Smith 14	LIFT	1/12/2020​
312​	John Smith 15	GW	1/12/2020​
312​	John Smith 15	FR LIFT	1/12/2020​

If it helps, here are my formulas in each column:

J:

=IFERROR(INDEX(F$3:F$38,AGGREGATE(15,6,(ROW($G$3:$G$38)-ROW($G$3)+1)/(ISNA(MATCH($B$3:$B$38&"|"&$C$3:$C$38,$G$3:$G$38&"|"&$H$3:$H$38,0))),ROWS(J$3:J3))),"")

K:

=IFERROR(INDEX(G$3:G$38,AGGREGATE(15,6,(ROW($G$3:$G$38)-ROW($G$3)+1)/(ISNA(MATCH($B$3:$B$38&"|"&$C$3:$C$38,$G$3:$G$38&"|"&$H$3:$H$38,0))),ROWS(K$3:K3))),"")

L:

=IFERROR(INDEX(H$3:H$38,AGGREGATE(15,6,(ROW($G$3:$G$38)-ROW($G$3)+1)/(ISNA(MATCH($B$3:$B$38&"|"&$C$3:$C$38,$G$3:$G$38&"|"&$H$3:$H$38,0))),ROWS(L$3:L3))),"")

I had done Ctrl + Shift + Enter as you see in screenshot. Just wondered where I went wrong? If worst come to worst I could always copy the actual sheet that has employee names where this would be utilized. I just did this on a separate sheet with John Smith names for confidentiality, but it's not like there are SSN's or phone numbers or anything super sensitive. Let me know if there might be more info I need to get you. Thank you and sorry.

 

[Fluff]

Oops, I got the match part the wrong way round

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1		Bid History List					Pasted List from New Bid Tab				Names contained in Pasted List NOT on history list
2	LM NO.	EMPLOYEE	JOB HELD	DATE		LM NO.	EMPLOYEE	JOB HELD		LM NO.	EMPLOYEE	JOB HELD
3	154	John Smith 1	GW	8/16/2020		154	John Smith 1	GW		980	John Smith 10	FR DMG RCP
4	410	John Smith 2	GW	3/28/2021		154	John Smith 1	GW		187	John Smith 11	DMG RCP
5	565	John Smith 3	GW	1/12/2020		154	John Smith 1	GW		336	John Smith 12	FR DMG RCP
6	963	John Smith 4	GW	1/12/2020		980	John Smith 10	GW		337	John Smith 13	DMG RCP
7	944	John Smith 5	GW	1/12/2020		980	John Smith 10	FR DMG RCP		565	John Smith 3	JAN
8	542	John Smith 6	GW	1/12/2020		980	John Smith 10	GW		542	John Smith 6	JAN
9	247	John Smith 7	GW	10/18/2020		187	John Smith 11	GW		422	John Smith 8	GW
10	422	John Smith 8	JAN	1/12/2020		187	John Smith 11	DMG RCP		422	John Smith 8	GW
11	71	John Smith 9	GW	2/14/2021		336	John Smith 12	GW		422	John Smith 8	GW
12	980	John Smith 10	GW	1/12/2020		336	John Smith 12	FR DMG RCP		71	John Smith 9	JAN
13	187	John Smith 11	GW	10/4/2020		337	John Smith 13	GW
14	336	John Smith 12	GW	1/12/2020		337	John Smith 13	DMG RCP
15	337	John Smith 13	TRAINER	1/12/2020		337	John Smith 13	GW
16	337	John Smith 13	GW	2/9/2020		410	John Smith 2	GW
17	337	John Smith 13	LIFT	1/10/2021		565	John Smith 3	JAN
18	415	John Smith 14	LIFT	1/12/2020		963	John Smith 4	GW
19	312	John Smith 15	GW	1/12/2020		944	John Smith 5	GW
20	312	John Smith 15	FR LIFT	5/10/2020		542	John Smith 6	GW
21	154	John Smith 1	GW	1/12/2020		542	John Smith 6	GW
22	410	John Smith 2	GW	1/12/2020		542	John Smith 6	JAN
23	565	John Smith 3	GW	1/12/2020		247	John Smith 7	GW
24	963	John Smith 4	GW	1/12/2020		247	John Smith 7	GW
25	944	John Smith 5	GW	1/12/2020		422	John Smith 8	GW
26	542	John Smith 6	GW	1/12/2020		422	John Smith 8	GW
27	247	John Smith 7	GW	1/12/2020		422	John Smith 8	GW
28	422	John Smith 8	JAN	1/12/2020		71	John Smith 9	JAN
29	71	John Smith 9	GW	1/12/2020		71	John Smith 9	GW
30	980	John Smith 10	GW	1/12/2020
31	187	John Smith 11	GW	1/12/2020
32	336	John Smith 12	GW	1/12/2020
33	337	John Smith 13	TRAINER	1/12/2020
34	337	John Smith 13	GW	1/12/2020
35	337	John Smith 13	LIFT	1/12/2020
36	415	John Smith 14	LIFT	1/12/2020
37	312	John Smith 15	GW	1/12/2020
38	312	John Smith 15	FR LIFT	1/12/2020
Data

Cell Formulas
Range	Formula
J3:L28	J3	=IFERROR(INDEX(F$3:F$38,AGGREGATE(15,6,(ROW($G$3:$G$38)-ROW($G$3)+1)/(ISNA(MATCH($G$3:$G$38&"|"&$H$3:$H$38,$B$3:$B$38&"|"&$C$3:$C$38,0)))/($G$3:$G$38<>""),ROWS(J$3:J3))),"")

 

[bh24524]

Yep that fixed it. I even used on the actual live sheet and it appears to be working. Awesome show man!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[bh24524]

lol the wheels in my head are already spinning on how to utilize this in a couple other slightly different processes/scenarios which might alter the formula. Every month, I do a comparison between our database and the online system to employees rate of pay and also their position code which just designates their job title and schedule. I normally use conditional formatting which is more efficient than checking line item by line item of course. But if CF would be light speed, this would actually be warp speed in comparison for what I'm looking to do. I'll post something next week when I have some time, but would you prefer I post it here or make a new post entirely?

 

[Fluff]

Probably best to start a new thread.

 

[Rahul1987]

D13 =INDEX($H$3:$H$7,SMALL(IF(ISNA(MATCH($H$3:$H$7,$I$3:$I$7,0)),ROW($H$3:$H$7)-ROW($H$3)+1),ROWS(D$13:D13)))

 

[erikcg123]

Hi, I have very little excel or sheets skills...can someone do the above formula for me for these two lists? I would GREATLY appreciate it!

Dropbox - File Deleted - Simplify your life

www.dropbox.com