Comparison Formula

[limo2088]

How can I get excel to spit out who I am missing? Please see my example below, So I have a full roster in Column C, who are "logged into the system" Column B, however some are missing. So instead of me doing a simple sort than comparing is there something I can use and have the results populate in Column D? Example below I did manually.

Mentor Checker.csv
	B	C	D
2	Logged Into System	Full Roster	Missing In System
3	Andrew Pickett	Andrew Pickett	Irvin Jones
4	Anita Russell	Anita Russell	Cierra Barnes
5	Benjamin Kratky	Benjamin Kratky	Phillip Carroll
6	Brandon Brownlee	Brandon Brownlee
7	Derek Whipkey	Cierra Barnes
8	Derrick Marshall	Derek Whipkey
9	JASON GRACE	Derrick Marshall
10	Jayda Nix	Irvin Jones
11	Jessie Muheisien	JASON GRACE
12	Johnathan MCRAE	Jayda Nix
13	Jonathon Honaker	Jessie Muheisien
14	Kelvonne King	Johnathan MCRAE
15	Lamar Ware	Jonathon Honaker
16	Lashae Johnson	Kelvonne King
17	Lawrence Lambert	Lamar Ware
18	Marlesha Berry	Lashae Johnson
19	PATRICK CONNORS	Lawrence Lambert
20	Quinton Clemons	Marlesha Berry
21	Roger Overfield	PATRICK CONNORS
22	Travis Snider	Phillip Carroll
23		Quinton Clemons
24		Roger Overfield
25		Travis Snider
Mentor Checker

 

[alansidman]

Load each column as an individual column to Power Query/Get and Transform Data found on the Data Tab.

Join the two tables/queries in a new query

let
    Source = Table.NestedJoin(FullRoster, {"Full Roster"}, LoggedIn, {"Logged Into System"}, "LoggedIn", JoinKind.LeftAnti),
    #"Removed Columns" = Table.RemoveColumns(Source,{"LoggedIn"})
in
    #"Removed Columns"

Book2
	B	C	D
1	Logged Into System	Full Roster	Full Roster
2	Andrew Pickett	Andrew Pickett	Cierra Barnes
3	Anita Russell	Anita Russell	Irvin Jones
4	Benjamin Kratky	Benjamin Kratky	Phillip Carroll
5	Brandon Brownlee	Brandon Brownlee
6	Derek Whipkey	Cierra Barnes
7	Derrick Marshall	Derek Whipkey
8	JASON GRACE	Derrick Marshall
9	Jayda Nix	Irvin Jones
10	Jessie Muheisien	JASON GRACE
11	Johnathan MCRAE	Jayda Nix
12	Jonathon Honaker	Jessie Muheisien
13	Kelvonne King	Johnathan MCRAE
14	Lamar Ware	Jonathon Honaker
15	Lashae Johnson	Kelvonne King
16	Lawrence Lambert	Lamar Ware
17	Marlesha Berry	Lashae Johnson
18	PATRICK CONNORS	Lawrence Lambert
19	Quinton Clemons	Marlesha Berry
20	Roger Overfield	PATRICK CONNORS
21	Travis Snider	Phillip Carroll
22		Quinton Clemons
23		Roger Overfield
24		Travis Snider
Sheet1

 

[Peter_SSs]

21 11 15.xlsm
	B	C	D
2	Logged Into System	Full Roster	Missing In System
3	Andrew Pickett	Andrew Pickett	Cierra Barnes
4	Anita Russell	Anita Russell	Irvin Jones
5	Benjamin Kratky	Benjamin Kratky	Phillip Carroll
6	Brandon Brownlee	Brandon Brownlee
7	Derek Whipkey	Cierra Barnes
8	Derrick Marshall	Derek Whipkey
9	JASON GRACE	Derrick Marshall
10	Jayda Nix	Irvin Jones
11	Jessie Muheisien	JASON GRACE
12	Johnathan MCRAE	Jayda Nix
13	Jonathon Honaker	Jessie Muheisien
14	Kelvonne King	Johnathan MCRAE
15	Lamar Ware	Jonathon Honaker
16	Lashae Johnson	Kelvonne King
17	Lawrence Lambert	Lamar Ware
18	Marlesha Berry	Lashae Johnson
19	PATRICK CONNORS	Lawrence Lambert
20	Quinton Clemons	Marlesha Berry
21	Roger Overfield	PATRICK CONNORS
22	Travis Snider	Phillip Carroll
23		Quinton Clemons
24		Roger Overfield
25		Travis Snider
Missing

Cell Formulas
Range	Formula
D3:D9	D3	=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(C$3:C$25)/ISNA(MATCH(C$3:C$25,B$3:B$22,0)),ROWS(D$2:D2))),"")

 

[limo2088]

21 11 15.xlsm
	B	C	D
2	Logged Into System	Full Roster	Missing In System
3	Andrew Pickett	Andrew Pickett	Cierra Barnes
4	Anita Russell	Anita Russell	Irvin Jones
5	Benjamin Kratky	Benjamin Kratky	Phillip Carroll
6	Brandon Brownlee	Brandon Brownlee
7	Derek Whipkey	Cierra Barnes
8	Derrick Marshall	Derek Whipkey
9	JASON GRACE	Derrick Marshall
10	Jayda Nix	Irvin Jones
11	Jessie Muheisien	JASON GRACE
12	Johnathan MCRAE	Jayda Nix
13	Jonathon Honaker	Jessie Muheisien
14	Kelvonne King	Johnathan MCRAE
15	Lamar Ware	Jonathon Honaker
16	Lashae Johnson	Kelvonne King
17	Lawrence Lambert	Lamar Ware
18	Marlesha Berry	Lashae Johnson
19	PATRICK CONNORS	Lawrence Lambert
20	Quinton Clemons	Marlesha Berry
21	Roger Overfield	PATRICK CONNORS
22	Travis Snider	Phillip Carroll
23		Quinton Clemons
24		Roger Overfield
25		Travis Snider
Missing

Cell Formulas
Range	Formula
D3:D9	D3	=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(C$3:C$25)/ISNA(MATCH(C$3:C$25,B$3:B$22,0)),ROWS(D$2:D2))),"")

Any Idea why this isnt working?

Alpha2.xlsx
	A	B	C
7	ID		#NAME?
8	Joseph Micco		#NAME?
9	Jonathon Honaker		#NAME?
10	Jakob Lenos		#NAME?
11	Quinton Clemons		#NAME?
12	Lee Donnelly		#NAME?
13	Cassidy Kaurich		#NAME?
14	Johnathan McRae		#NAME?
15	Colton Starcher		#NAME?
16	Derrick Marshall		#NAME?
17	Alexis Torres		#NAME?
18	Regis Wilkins		#NAME?
19	Dylan Stewart		#NAME?
20	Asia Dickey		#NAME?
21	Ronald Knight		#NAME?
22	Donald McGhee jr		#NAME?
23	Lawrence Lambert		#NAME?
24	Jayda Nix		#NAME?
25	Shon Byars		#NAME?
26	Travis Snider		#NAME?
27	Felix Muniz Ramos		#NAME?
28	Luis Torres		#NAME?
29	LaShae Johnson		#NAME?
Sheet1

Cell Formulas
Range	Formula
C7:C29	C7	=FERROR(INDEX($A$8:$A$43,AGGREGATE(15,6,(ROW($A$8:$A$43)-ROW($A$8)+1)/(COUNTIFS($A$8:$A$43,"<"&$A$8:$A$43)+1=ROWS(C$3:C9))/($A$8:$A$43<>""),1)),"")

 

[Candyman8019]

At the front of your formula you have 'FERROR' rather than 'IFERROR'

 

[limo2088]

At the front of your formula you have 'FERROR' rather than 'IFERROR'

wow, thanks for that, now I am getting this, why does it not count everyone?

Alpha2.xlsx
	A	B	C
7	ID		Dylan Stewart
8	Joseph Micco		Felix Muniz Ramos
9	Jonathon Honaker		Jakob Lenos
10	Jakob Lenos		Jayda Nix
11	Quinton Clemons		Johnathan McRae
12	Lee Donnelly		Jonathon Honaker
13	Cassidy Kaurich		Joseph Micco
14	Johnathan McRae		LaShae Johnson
15	Colton Starcher		Lawrence Lambert
16	Derrick Marshall		Lee Donnelly
17	Alexis Torres		Luis Torres
18	Regis Wilkins		Quinton Clemons
19	Dylan Stewart		Regis Wilkins
20	Asia Dickey		Ronald Knight
21	Ronald Knight		Shon Byars
22	Donald McGhee jr		Travis Snider
23	Lawrence Lambert
24	Jayda Nix
25	Shon Byars
26	Travis Snider
27	Felix Muniz Ramos
28	Luis Torres
29	LaShae Johnson
Sheet1

Cell Formulas
Range	Formula
C7:C29	C7	=IFERROR(INDEX($A$8:$A$43,AGGREGATE(15,6,(ROW($A$8:$A$43)-ROW($A$8)+1)/(COUNTIFS($A$8:$A$43,"<"&$A$8:$A$43)+1=ROWS(C$3:C9))/($A$8:$A$43<>""),1)),"")

 

[Fluff]

As this is a totally different question from your OP, please start a new thread, explaining what you are trying to do. Thanks

 

[alansidman]

limo
Did you look at my solution? Any thoughts? Feedback?

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.