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.
 

bh24524

Active Member
Joined
Dec 11, 2008
Messages
256
Office Version
  1. 2013
  2. 2007
Bid History ListPasted List from New Bid TabNames contained in Pasted List NOT on history list
LM NO.EMPLOYEEJOB HELDDATELM NO.EMPLOYEEJOB HELDLM NO.EMPLOYEEJOB HELD
154​
John Smith 1GW
8/16/2020​
154​
John Smith 1GW
410​
John Smith 2GW
3/28/2021​
410​
John Smith 2GW
565​
John Smith 3GW
1/12/2020​
565​
John Smith 3GW
963​
John Smith 4GW
1/12/2020​
963​
John Smith 4GW
944​
John Smith 5GW
1/12/2020​
944​
John Smith 5GW
542​
John Smith 6GW
1/12/2020​
542​
John Smith 6GW
247​
John Smith 7GW
10/18/2020​
247​
John Smith 7GW
422​
John Smith 8JAN
1/12/2020​
422​
John Smith 8JAN
71​
John Smith 9GW
2/14/2021​
71​
John Smith 9GW
980​
John Smith 10GW
1/12/2020​
980​
John Smith 10GW
187​
John Smith 11GW
10/4/2020​
187​
John Smith 11GW
336​
John Smith 12GW
1/12/2020​
336​
John Smith 12GW
337​
John Smith 13TRAINER
1/12/2020​
337​
John Smith 13TRAINER
337​
John Smith 13GW
2/9/2020​
154​
John Smith 1JAN
337​
John Smith 13LIFT
1/10/2021​
542​
John Smith 6FR JAN
415​
John Smith 14LIFT
1/12/2020​
422​
John Smith 8BALER
312​
John Smith 15GW
1/12/2020​
337​
John Smith 13DMG RCP
312​
John Smith 15FR LIFT
5/10/2020​
980​
John Smith 10FR DMG RCP

Does that help?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,772
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, how about
+Fluff 1.xlsm
ABCDEFGHIJKL
1Bid History ListPasted List from New Bid TabNames contained in Pasted List NOT on history list
2LM NO.EMPLOYEEJOB HELDDATELM NO.EMPLOYEEJOB HELDLM NO.EMPLOYEEJOB HELD
3154John Smith 1GW16/08/2020154John Smith 1GW154John Smith 1JAN
4410John Smith 2GW28/03/2021410John Smith 2GW542John Smith 6FR JAN
5565John Smith 3GW12/01/2020565John Smith 3GW422John Smith 8BALER
6963John Smith 4GW12/01/2020963John Smith 4GW337John Smith 13DMG RCP
7944John Smith 5GW12/01/2020944John Smith 5GW980John Smith 10FR DMG RCP
8542John Smith 6GW12/01/2020542John Smith 6GW   
9247John Smith 7GW18/10/2020247John Smith 7GW   
10422John Smith 8JAN12/01/2020422John Smith 8JAN
1171John Smith 9GW14/02/202171John Smith 9GW
12980John Smith 10GW12/01/2020980John Smith 10GW
13187John Smith 11GW04/10/2020187John Smith 11GW
14336John Smith 12GW12/01/2020336John Smith 12GW
15337John Smith 13TRAINER12/01/2020337John Smith 13TRAINER
16337John Smith 13GW09/02/2020154John Smith 1JAN
17337John Smith 13LIFT10/01/2021542John Smith 6FR JAN
18415John Smith 14LIFT12/01/2020422John Smith 8BALER
19312John Smith 15GW12/01/2020337John Smith 13DMG RCP
20312John Smith 15FR LIFT10/05/2020980John Smith 10FR DMG RCP
21
Data
Cell Formulas
RangeFormula
J3:L9J3=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

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

1620400358388.png


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 ListPasted List from New Bid TabNames contained in Pasted List NOT on history list
LM NO.EMPLOYEEJOB HELDDATELM NO.EMPLOYEEJOB HELDLM NO.EMPLOYEEJOB HELD
154​
John Smith 1GW
8/16/2020​
154​
John Smith 1GW
154​
John Smith 1GW
410​
John Smith 2GW
3/28/2021​
154​
John Smith 1GW
187​
John Smith 11DMG RCP
565​
John Smith 3GW
1/12/2020​
154​
John Smith 1GW
337​
John Smith 13GW
963​
John Smith 4GW
1/12/2020​
980​
John Smith 10GW
565​
John Smith 3JAN
944​
John Smith 5GW
1/12/2020​
980​
John Smith 10FR DMG RCP
963​
John Smith 4GW
542​
John Smith 6GW
1/12/2020​
980​
John Smith 10GW
944​
John Smith 5GW
247​
John Smith 7GW
10/18/2020​
187​
John Smith 11GW
542​
John Smith 6GW
422​
John Smith 8JAN
1/12/2020​
187​
John Smith 11DMG RCP
247​
John Smith 7GW
71​
John Smith 9GW
2/14/2021​
336​
John Smith 12GW
71​
John Smith 9JAN
980​
John Smith 10GW
1/12/2020​
336​
John Smith 12FR DMG RCP
0​
0​
0​
187​
John Smith 11GW
10/4/2020​
337​
John Smith 13GW
0​
0​
0​
336​
John Smith 12GW
1/12/2020​
337​
John Smith 13DMG RCP
0​
0​
0​
337​
John Smith 13TRAINER
1/12/2020​
337​
John Smith 13GW
0​
0​
0​
337​
John Smith 13GW
2/9/2020​
410​
John Smith 2GW
0​
0​
0​
337​
John Smith 13LIFT
1/10/2021​
565​
John Smith 3JAN
415​
John Smith 14LIFT
1/12/2020​
963​
John Smith 4GW
312​
John Smith 15GW
1/12/2020​
944​
John Smith 5GW
312​
John Smith 15FR LIFT
5/10/2020​
542​
John Smith 6GW
154​
John Smith 1GW
1/12/2020​
542​
John Smith 6GW
410​
John Smith 2GW
1/12/2020​
542​
John Smith 6JAN
565​
John Smith 3GW
1/12/2020​
247​
John Smith 7GW
963​
John Smith 4GW
1/12/2020​
247​
John Smith 7GW
944​
John Smith 5GW
1/12/2020​
422​
John Smith 8GW
542​
John Smith 6GW
1/12/2020​
422​
John Smith 8GW
247​
John Smith 7GW
1/12/2020​
422​
John Smith 8GW
422​
John Smith 8JAN
1/12/2020​
71​
John Smith 9JAN
71​
John Smith 9GW
1/12/2020​
71​
John Smith 9GW
980​
John Smith 10GW
1/12/2020​
187​
John Smith 11GW
1/12/2020​
336​
John Smith 12GW
1/12/2020​
337​
John Smith 13TRAINER
1/12/2020​
337​
John Smith 13GW
1/12/2020​
337​
John Smith 13LIFT
1/12/2020​
415​
John Smith 14LIFT
1/12/2020​
312​
John Smith 15GW
1/12/2020​
312​
John Smith 15FR LIFT
1/12/2020​

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

J:
Excel Formula:
=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:
Excel Formula:
=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:
Excel Formula:
=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,772
Office Version
  1. 365
Platform
  1. Windows
Oops, I got the match part the wrong way round
+Fluff 1.xlsm
ABCDEFGHIJKL
1Bid History ListPasted List from New Bid TabNames contained in Pasted List NOT on history list
2LM NO.EMPLOYEEJOB HELDDATELM NO.EMPLOYEEJOB HELDLM NO.EMPLOYEEJOB HELD
3154John Smith 1GW8/16/2020154John Smith 1GW980John Smith 10FR DMG RCP
4410John Smith 2GW3/28/2021154John Smith 1GW187John Smith 11DMG RCP
5565John Smith 3GW1/12/2020154John Smith 1GW336John Smith 12FR DMG RCP
6963John Smith 4GW1/12/2020980John Smith 10GW337John Smith 13DMG RCP
7944John Smith 5GW1/12/2020980John Smith 10FR DMG RCP565John Smith 3JAN
8542John Smith 6GW1/12/2020980John Smith 10GW542John Smith 6JAN
9247John Smith 7GW10/18/2020187John Smith 11GW422John Smith 8GW
10422John Smith 8JAN1/12/2020187John Smith 11DMG RCP422John Smith 8GW
1171John Smith 9GW2/14/2021336John Smith 12GW422John Smith 8GW
12980John Smith 10GW1/12/2020336John Smith 12FR DMG RCP71John Smith 9JAN
13187John Smith 11GW10/4/2020337John Smith 13GW   
14336John Smith 12GW1/12/2020337John Smith 13DMG RCP   
15337John Smith 13TRAINER1/12/2020337John Smith 13GW   
16337John Smith 13GW2/9/2020410John Smith 2GW   
17337John Smith 13LIFT1/10/2021565John Smith 3JAN   
18415John Smith 14LIFT1/12/2020963John Smith 4GW   
19312John Smith 15GW1/12/2020944John Smith 5GW   
20312John Smith 15FR LIFT5/10/2020542John Smith 6GW   
21154John Smith 1GW1/12/2020542John Smith 6GW   
22410John Smith 2GW1/12/2020542John Smith 6JAN   
23565John Smith 3GW1/12/2020247John Smith 7GW   
24963John Smith 4GW1/12/2020247John Smith 7GW   
25944John Smith 5GW1/12/2020422John Smith 8GW   
26542John Smith 6GW1/12/2020422John Smith 8GW   
27247John Smith 7GW1/12/2020422John Smith 8GW   
28422John Smith 8JAN1/12/202071John Smith 9JAN   
2971John Smith 9GW1/12/202071John Smith 9GW
30980John Smith 10GW1/12/2020
31187John Smith 11GW1/12/2020
32336John Smith 12GW1/12/2020
33337John Smith 13TRAINER1/12/2020
34337John Smith 13GW1/12/2020
35337John Smith 13LIFT1/12/2020
36415John Smith 14LIFT1/12/2020
37312John Smith 15GW1/12/2020
38312John Smith 15FR LIFT1/12/2020
Data
Cell Formulas
RangeFormula
J3:L28J3=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))),"")
 
Solution

bh24524

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

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,772
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

bh24524

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

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,772
Office Version
  1. 365
Platform
  1. Windows
Probably best to start a new thread.
 

Rahul1987

Board Regular
Joined
Apr 10, 2021
Messages
80
Office Version
  1. 2010
Platform
  1. Windows
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)))
 

Forum statistics

Threads
1,143,907
Messages
5,721,436
Members
422,362
Latest member
elliotpat

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