Reference list of truck routes to roster

[Jsejms]

Hey guys,

I need to make a formula that compares the list of truck routes in column F to the days of my roster, and spits out the missing routes from roster (ideally a list under each day).
The roster is normally sorted out alphabetically by surname, but sometimes by truck routes.

 

[JamesCanale]

MrExcelPlayground2.xlsm
	A	B	C	D	E	F
23		3-Jun	4-Jun	5-Jun		Routes
24	Person1	A	A	K		A
25	Person2	B	C	G		B
26	Person3	B	B	F		C
27	Person4	C	I	A		D
28	Person5	F	J	D		E
29	Person6	D	H	F		F
30	Person7	E	M	E		G
31	Person8	G	O	J		H
32	Person9	G	B	K		I
33	Person10	H	A	O		J
34	Person11	A	B	M		K
35	Person12	J	C	D		L
36	Person13	L	E	G		M
37	Person14	P	G	H		N
38	Person15	N	Q	O		O
39						P
40	Missed Routes					Q
41		I	D	B
42		K	F	C
43		M	K	I
44		O	L	L
45		Q	N	N
46			P	P
47				Q
Sheet37

Cell Formulas
Range	Formula
B40:B45,D40:D47,C40:C46	B40	=UNIQUE(IF(XLOOKUP($F$24:$F$40,B24:B38,B24:B38,"XXX",0)="XXX",$F$24:$F$40,""))
Dynamic array formulas.

 

[Jsejms]

Thanks for your help.

I have implemented this into my full roster and get this strange anomaly.
The formula is entered into cell D55:AS55.
Why does it occur an empty space in d55 and e55, then again in f56.

 

[Fluff]

Another option

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1		44350	44351	44352	44353		Routes
2	Person1	A	A	K	A		A
3	Person2	B	C	G	B		B
4	Person3	B	B	F	C		C
5	Person4	C	I	A	D		D
6	Person5	F	J	D	E		E
7	Person6	D	H	F	F		F
8	Person7	E	M	E	G		G
9	Person8	G	O	J	H		H
10	Person9	G	B	K	I		I
11	Person10	H	A	O	J		J
12	Person11	A	B	M	K		K
13	Person12	J	C	D	L		L
14	Person13	L	E	G	M		M
15	Person14	P	G	H	N		N
16	Person15	N	Q	O	O		O
17
18
19	Missed Routes	I	D	B	None
20		K	F	C
21		M	K	I
22		O	L	L
23			N	N
24
List

Cell Formulas
Range	Formula
B19:B22,C19:D23	B19	=FILTER($G$2:$G$16,ISNA(XMATCH($G$2:$G$16,B2:B16,0)))
E19	E19	=FILTER($G$2:$G$16,ISNA(XMATCH($G$2:$G$16,E2:E16,0)),"None")
Dynamic array formulas.

 

[Jsejms]

You are amazing!! Thanks

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Jsejms]

Glad we could help & thanks for the feedback.

Any help on my second post? lol