How to see which employees are left off list of projects

[alm395]

I am trying to see which employees are left off a project that receives alerts.

I have a table with two columns (column A = "Roster", column B = "Emp") called "Current_Alerts". I have a second table called "Missing_Alerts" with the 44 projects listed in column A and the 14 employees listed across row 1.

Each of the 44 projects should have all 14 employees listed, but they do not. Some projects list 2 employees, and some list 10; they are all different. Is there a way to identify who is missing from each project? If there is a better layout to do this, I am open to any option. I just need to be able to identify the employees currently not listed on each project so that I may add them, without having to go one by one for each roster.

I have included a sample that shows 15 projects (instead of a total of 44).

Thank you for your time!

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Roster	Emp		ROSTERS	#	EMP 1	EMP 2	EMP 3	EMP 4	EMP 5	EMP 6	EMP 7	EMP 8	EMP 9	EMP 10	EMP 11	EMP 12	EMP 13	EMP 14
2	PROJECT 1	EMP 2		PROJECT 1	10
3	PROJECT 1	EMP 3		PROJECT 2	10
4	PROJECT 1	EMP 6		PROJECT 3	7
5	PROJECT 1	EMP 7		PROJECT 4	7
6	PROJECT 1	EMP 8		PROJECT 5	10
7	PROJECT 1	EMP 9		PROJECT 6	10
8	PROJECT 1	EMP 10		PROJECT 7	10
9	PROJECT 1	EMP 11		PROJECT 8	1
10	PROJECT 1	EMP 13		PROJECT 9	6
11	PROJECT 1	EMP 14		PROJECT 10	6
12	PROJECT 2	EMP 1		PROJECT 11	6
13	PROJECT 2	EMP 3		PROJECT 12	6
14	PROJECT 2	EMP 4		PROJECT 13	6
15	PROJECT 2	EMP 5		PROJECT 14	8
16	PROJECT 2	EMP 7		PROJECT 15	6
17	PROJECT 2	EMP 10
18	PROJECT 2	EMP 11
19	PROJECT 2	EMP 12
20	PROJECT 2	EMP 13
21	PROJECT 2	EMP 14
22	PROJECT 3	EMP 2
23	PROJECT 3	EMP 3
24	PROJECT 3	EMP 7
25	PROJECT 3	EMP 10
26	PROJECT 3	EMP 11
27	PROJECT 3	EMP 13
28	PROJECT 3	EMP 14
29	PROJECT 4	EMP 2
30	PROJECT 4	EMP 3
31	PROJECT 4	EMP 7
32	PROJECT 4	EMP 10
33	PROJECT 4	EMP 11
34	PROJECT 4	EMP 13
35	PROJECT 4	EMP 14
36	PROJECT 5	EMP 1
37	PROJECT 5	EMP 3
38	PROJECT 5	EMP 4
39	PROJECT 5	EMP 5
40	PROJECT 5	EMP 7
41	PROJECT 5	EMP 10
42	PROJECT 5	EMP 11
43	PROJECT 5	EMP 12
44	PROJECT 5	EMP 13
45	PROJECT 5	EMP 14
46	PROJECT 6	EMP 1
47	PROJECT 6	EMP 3
48	PROJECT 6	EMP 4
49	PROJECT 6	EMP 5
50	PROJECT 6	EMP 7
51	PROJECT 6	EMP 10
52	PROJECT 6	EMP 11
53	PROJECT 6	EMP 12
54	PROJECT 6	EMP 13
55	PROJECT 6	EMP 14
56	PROJECT 7	EMP 1
57	PROJECT 7	EMP 3
58	PROJECT 7	EMP 4
59	PROJECT 7	EMP 5
60	PROJECT 7	EMP 7
61	PROJECT 7	EMP 10
62	PROJECT 7	EMP 11
63	PROJECT 7	EMP 12
64	PROJECT 7	EMP 13
65	PROJECT 7	EMP 14
66	PROJECT 8	EMP 14
67	PROJECT 9	EMP 2
68	PROJECT 9	EMP 3
69	PROJECT 9	EMP 7
70	PROJECT 9	EMP 10
71	PROJECT 9	EMP 11
72	PROJECT 9	EMP 13
73	PROJECT 10	EMP 2
74	PROJECT 10	EMP 3
75	PROJECT 10	EMP 7
76	PROJECT 10	EMP 10
77	PROJECT 10	EMP 11
78	PROJECT 10	EMP 13
79	PROJECT 11	EMP 2
80	PROJECT 11	EMP 3
81	PROJECT 11	EMP 7
82	PROJECT 11	EMP 10
83	PROJECT 11	EMP 11
84	PROJECT 11	EMP 13
85	PROJECT 12	EMP 2
86	PROJECT 12	EMP 3
87	PROJECT 12	EMP 7
88	PROJECT 12	EMP 10
89	PROJECT 12	EMP 11
90	PROJECT 12	EMP 13
91	PROJECT 13	EMP 2
92	PROJECT 13	EMP 3
93	PROJECT 13	EMP 7
94	PROJECT 13	EMP 10
95	PROJECT 13	EMP 11
96	PROJECT 13	EMP 13
97	PROJECT 14	EMP 2
98	PROJECT 14	EMP 3
99	PROJECT 14	EMP 7
100	PROJECT 14	EMP 9
101	PROJECT 14	EMP 10
102	PROJECT 14	EMP 11
103	PROJECT 14	EMP 13
104	PROJECT 14	EMP 14
105	PROJECT 15	EMP 2
106	PROJECT 15	EMP 3
107	PROJECT 15	EMP 7
108	PROJECT 15	EMP 10
109	PROJECT 15	EMP 11
110	PROJECT 15	EMP 13
Sheet1

Cell Formulas
Range	Formula
A3:A11,A106:A110,A98:A104,A92:A96,A86:A90,A80:A84,A74:A78,A68:A72,B66,A57:A65,A47:A55,A37:A45,A30:A35,A23:A28,A13:A21	A3	=A2
E2:E16	E2	=IFERROR(COUNTIF(Current_Alerts[#All],D2),"-")

 

[James006]

Hi,

Have you tried to Insert a Pivot Table ... to get all the possible analytical flexibility in the world ?

 

[alm395]

Hi,

Have you tried to Insert a Pivot Table ... to get all the possible analytical flexibility in the world ?

Nope. I was overthinking it. I knew there was an easy solution that I just wasn't thinking of.

Thank you!

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[alm395]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I had no idea! I also had no idea what version I was on. Based on Account info in Excel, it looks like I was recently upgraded to 365.

 

[Fluff]

Thanks for that.
As long as columns D:S is not a proper table, you could also use this

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Roster	Emp		ROSTERS	#	EMP 1	EMP 2	EMP 3	EMP 4	EMP 5	EMP 6	EMP 7	EMP 8	EMP 9	EMP 10	EMP 11	EMP 12	EMP 13	EMP 14
2	PROJECT 1	EMP 2		PROJECT 1	10	X			X	X							X
3	PROJECT 1	EMP 3		PROJECT 2	10		X				X		X	X
4	PROJECT 1	EMP 6		PROJECT 3	7	X			X	X	X		X	X			X
5	PROJECT 1	EMP 7		PROJECT 4	7	X			X	X	X		X	X			X
6	PROJECT 1	EMP 8		PROJECT 5	10		X				X		X	X
7	PROJECT 1	EMP 9		PROJECT 6	10		X				X		X	X
8	PROJECT 1	EMP 10		PROJECT 7	10		X				X		X	X
9	PROJECT 1	EMP 11		PROJECT 8	1	X	X	X	X	X	X	X	X	X	X	X	X	X
10	PROJECT 1	EMP 13		PROJECT 9	6	X			X	X	X		X	X			X		X
11	PROJECT 1	EMP 14		PROJECT 10	6	X			X	X	X		X	X			X		X
12	PROJECT 2	EMP 1		PROJECT 11	6	X			X	X	X		X	X			X		X
13	PROJECT 2	EMP 3		PROJECT 12	6	X			X	X	X		X	X			X		X
14	PROJECT 2	EMP 4		PROJECT 13	6	X			X	X	X		X	X			X		X
15	PROJECT 2	EMP 5		PROJECT 14	8	X			X	X	X		X				X
16	PROJECT 2	EMP 7		PROJECT 15	6	X			X	X	X		X	X			X		X
17	PROJECT 2	EMP 10
18	PROJECT 2	EMP 11
19	PROJECT 2	EMP 12
20	PROJECT 2	EMP 13
21	PROJECT 2	EMP 14
22	PROJECT 3	EMP 2
23	PROJECT 3	EMP 3
24	PROJECT 3	EMP 7
25	PROJECT 3	EMP 10
26	PROJECT 3	EMP 11
27	PROJECT 3	EMP 13
28	PROJECT 3	EMP 14
29	PROJECT 4	EMP 2
30	PROJECT 4	EMP 3
31	PROJECT 4	EMP 7
32	PROJECT 4	EMP 10
33	PROJECT 4	EMP 11
34	PROJECT 4	EMP 13
35	PROJECT 4	EMP 14
36	PROJECT 5	EMP 1
37	PROJECT 5	EMP 3
38	PROJECT 5	EMP 4
39	PROJECT 5	EMP 5
40	PROJECT 5	EMP 7
41	PROJECT 5	EMP 10
42	PROJECT 5	EMP 11
43	PROJECT 5	EMP 12
44	PROJECT 5	EMP 13
45	PROJECT 5	EMP 14
46	PROJECT 6	EMP 1
47	PROJECT 6	EMP 3
48	PROJECT 6	EMP 4
49	PROJECT 6	EMP 5
50	PROJECT 6	EMP 7
51	PROJECT 6	EMP 10
52	PROJECT 6	EMP 11
53	PROJECT 6	EMP 12
54	PROJECT 6	EMP 13
55	PROJECT 6	EMP 14
56	PROJECT 7	EMP 1
57	PROJECT 7	EMP 3
58	PROJECT 7	EMP 4
59	PROJECT 7	EMP 5
60	PROJECT 7	EMP 7
61	PROJECT 7	EMP 10
62	PROJECT 7	EMP 11
63	PROJECT 7	EMP 12
64	PROJECT 7	EMP 13
65	PROJECT 7	EMP 14
66	PROJECT 8	EMP 14
67	PROJECT 9	EMP 2
68	PROJECT 9	EMP 3
69	PROJECT 9	EMP 7
70	PROJECT 9	EMP 10
71	PROJECT 9	EMP 11
72	PROJECT 9	EMP 13
73	PROJECT 10	EMP 2
74	PROJECT 10	EMP 3
75	PROJECT 10	EMP 7
76	PROJECT 10	EMP 10
77	PROJECT 10	EMP 11
78	PROJECT 10	EMP 13
79	PROJECT 11	EMP 2
80	PROJECT 11	EMP 3
81	PROJECT 11	EMP 7
82	PROJECT 11	EMP 10
83	PROJECT 11	EMP 11
84	PROJECT 11	EMP 13
85	PROJECT 12	EMP 2
86	PROJECT 12	EMP 3
87	PROJECT 12	EMP 7
88	PROJECT 12	EMP 10
89	PROJECT 12	EMP 11
90	PROJECT 12	EMP 13
91	PROJECT 13	EMP 2
92	PROJECT 13	EMP 3
93	PROJECT 13	EMP 7
94	PROJECT 13	EMP 10
95	PROJECT 13	EMP 11
96	PROJECT 13	EMP 13
97	PROJECT 14	EMP 2
98	PROJECT 14	EMP 3
99	PROJECT 14	EMP 7
100	PROJECT 14	EMP 9
101	PROJECT 14	EMP 10
102	PROJECT 14	EMP 11
103	PROJECT 14	EMP 13
104	PROJECT 14	EMP 14
105	PROJECT 15	EMP 2
106	PROJECT 15	EMP 3
107	PROJECT 15	EMP 7
108	PROJECT 15	EMP 10
109	PROJECT 15	EMP 11
110	PROJECT 15	EMP 13
Main

Cell Formulas
Range	Formula
E2:E16	E2	=IFERROR(COUNTIF(Current_Alerts[#All],D2),"-")
F2:S16	F2	=IF(ISNA(MATCH($F$1:$S$1,FILTER(Current_Alerts[Emp],Current_Alerts[Roster]=D2),0)),"X","")
A3:A11,A106:A110,A98:A104,A92:A96,A86:A90,A80:A84,A74:A78,A68:A72,B66,A57:A65,A47:A55,A37:A45,A30:A35,A23:A28,A13:A21	A3	=A2
Dynamic array formulas.