I have a list of who has read an item, I need a list of who has not

[John McCuaig]

I have a power app for my team briefs that are sent to Ipads and desktops. It exports to excel a list of who has read it but I have almost 160 staff and several briefs. I can filter by subject or by person but would like a way of seeing who has not read the subject. At present its impossible to quickly check who still needs to read the brief, if i filter a title it can give me 150 names but who are the missing ones? Its pretty much unusable for tracking at the moment. From picture attached-column one is the title of the brief, column two is the employee name. I would like a way of looking up a brief, for example in below screenshot "DM Brief- 01/21" and it will give me a list of who still has not read it so we can remind them. I can make a master list of employee names for a lookup.
Any advice would be greatly appreciated,thanks.

 

[Peter_SSs]

Welcome to the MrExcel board!

There is a lot of manual typing to set that up to test! I suggest that you investigate XL2BB for providing sample data to make it easier for your potential helpers.

Do you have the LET function in your Excel version? If so, see if you can adapt this.

John McCuaig.xlsm
	A	B						H	I	J
1	Title	Employee Name								Not read by
2	Title 1	Emp11							Title 1	Emp10
3	Title 2	Emp12								Emp7
4	Title 3	Emp11								Emp2
5	Title 1	Emp17								Emp5
6	Title 5	Emp10								Emp3
7	Title 1	Emp6
8	Title 1	Emp12
9	Title 3	Emp7
10	Title 4	Emp2
11	Title 2	Emp10
12	Title 1	Emp11
13	Title 2	Emp5
14	Title 1	Emp16
15	Title 4	Emp3
16	Title 5	Emp3
17
Sheet1

Cell Formulas
Range	Formula
J2:J6	J2	=LET(Names,Table1[Employee Name],UNIQUE(FILTER(Names,ISNA(MATCH(Names,FILTER(Names,Table1[Title]=I2),0)))))
Dynamic array formulas.

 

[John McCuaig]

Thanks Peter, i have done a mini sheet, only a snap shot as kept saying too big. Will have a look at your option a bit later as my dog is giving me the big brown eyes at the minute for her walk! My knowledge and experience is pretty poor so any help is appreciated. Cheers, John

Book1
	A	B	C
1	Title	Employee Name	Date Stamp
2	DM Brief- 01/21	John McCuaig	15/02/2021
3	COVID-01/21 C19 Brief	John McCuaig	15/02/2021
4	PERFORMANCE 01/21	John McCuaig	15/02/2021
5	DM Brief- 01/21	John McCuaig	15/02/2021
6	COVID-01/21 C19 Brief	John McCuaig	15/02/2021
7	SAFETY Performance 01/21	Nick De jersey	15/02/2021
8	COVID-01/21 C19 Brief	John McCuaig	15/02/2021
9	PERFORMANCE 01/21	John McCuaig	15/02/2021
10	SAFETY Performance 01/21	John McCuaig	15/02/2021
11	PERFORMANCE 02/21	John McCuaig	15/02/2021
12	PERFORMANCE 01/21	John McCuaig	15/02/2021
13	PERFORMANCE 01/21	David Steadman	15/02/2021
14	PERFORMANCE 02/21	David Steadman	15/02/2021
15	PERFORMANCE 01/21	David Steadman	15/02/2021
16	COVID-01/21 C19 Brief	Raymond Burt	15/02/2021
17	DM Brief- 01/21	Raymond Burt	15/02/2021
18	SAFETY Performance 01/21	David Steadman	15/02/2021
19	PERFORMANCE 01/21	Raymond Burt	15/02/2021
20	COVID-01/21 C19 Brief	Toby Pielow	16/02/2021
21	PERFORMANCE 01/21	Nick De jersey	16/02/2021
22	DM Brief- 01/21	Nick De jersey	16/02/2021
23	DM Brief- 01/21	Nick De jersey	16/02/2021
24	COVID-01/21 C19 Brief	Richard Watson	16/02/2021
25	COVID-01/21 C19 Brief	Nick De jersey	17/02/2021
26	PERFORMANCE 01/21	Nick De jersey	17/02/2021
27	PERFORMANCE 02/21	Nick De jersey	17/02/2021
28	SAFETY Performance 01/21	Nick De jersey	17/02/2021
29	COVID-01/21 C19 Brief	Martin Mepham-Foster	18/02/2021
30	COVID-01/21 C19 Brief	John McCuaig	22/02/2021
31	COVID-01/21 C19 Brief	David Richardson	23/02/2021
32	Depot Brief 01/21	John McCuaig	24/02/2021
33	Electrostar Brief 01/21	John McCuaig	24/02/2021
34	Covid Brief 23/02/21	John McCuaig	24/02/2021
35	Depot Brief 01/21	John McCuaig	24/02/2021
36	Electrostar Brief 01/21	John McCuaig	24/02/2021
37	Covid Brief 23/02/21	John McCuaig	24/02/2021
38	Covid Brief 23/02/21	Chris Farrant	24/02/2021
39	Depot Brief 01/21	Chris Farrant	24/02/2021
40	Electrostar Brief 01/21	Chris Farrant	24/02/2021
41	Covid Brief 23/02/21	Glen Harrison	24/02/2021
42	Depot Brief 01/21	Glen Harrison	24/02/2021
43	Electrostar Brief 01/21	Glen Harrison	24/02/2021
44	Depot Brief 01/21	Ryan Bower	24/02/2021
45	Covid Brief 23/02/21	Ryan Bower	24/02/2021
46	Electrostar Brief 01/21	Ryan Bower	24/02/2021
47	Covid Brief 23/02/21	Richard Watson	24/02/2021
48	Electrostar Brief 01/21	Richard Watson	24/02/2021
49	Depot Brief 01/21	Richard Watson	24/02/2021
50	Electrostar Brief 01/21	Steff Jones	24/02/2021
51	Depot Brief 01/21	Steff Jones	24/02/2021
52	Covid Brief 23/02/21	Steff Jones	24/02/2021
53	Covid Brief 23/02/21	Kevin Leggett	25/02/2021
54	Covid Brief 23/02/21	Larry Franco	25/02/2021
55	Depot Brief 01/21	Martin Day	25/02/2021
56	Covid Brief 23/02/21	Daniel Barber	25/02/2021
57	Electrostar Brief 01/21	Nick De jersey	25/02/2021
58	Electrostar Brief 01/21	Nick De jersey	25/02/2021
59	Covid Brief 23/02/21	Nick De jersey	25/02/2021
60	Covid Brief 23/02/21	Nick De jersey	25/02/2021
61	Electrostar Brief 01/21	Nick De jersey	25/02/2021
62	Covid Brief 23/02/21	Katrina Rose Allen	25/02/2021
63	Depot Brief 01/21	Katrina Rose Allen	25/02/2021
64	Electrostar Brief 01/21	Katrina Rose Allen	25/02/2021
65	Covid Brief 23/02/21	Paul Bailey	25/02/2021
66	Covid Brief 23/02/21	Nick De jersey	25/02/2021
67	Depot Brief 01/21	Nick De jersey	25/02/2021
68	Covid Brief 23/02/21	Neil Edmunds	25/02/2021
69	Depot Brief 01/21	Neil Edmunds	25/02/2021
70	Electrostar Brief 01/21	Neil Edmunds	25/02/2021
71	Covid Brief 23/02/21	Richard Budd	25/02/2021
72	Depot Brief 01/21	Richard Budd	25/02/2021
73	Electrostar Brief 01/21	Richard Budd	25/02/2021
74	Electrostar Brief 01/21	Robert Soper	26/02/2021
75	Depot Brief 01/21	Robert Soper	26/02/2021
76	Covid Brief 23/02/21	Robert Soper	26/02/2021
77	Covid Brief 23/02/21	Fred Wells	26/02/2021
78	Electrostar Brief 01/21	Fred Wells	26/02/2021
79	Depot Brief 01/21	Fred Wells	26/02/2021
80	Covid Brief 23/02/21	Barry Lucas	26/02/2021
81	Depot Brief 01/21	David Steadman	26/02/2021
82	Covid Brief 23/02/21	David Steadman	26/02/2021
83	Electrostar Brief 01/21	David Steadman	26/02/2021
84	Covid Brief 23/02/21	Nick De jersey	26/02/2021
85	Depot Brief 01/21	Nick De jersey	26/02/2021
86	Covid Brief 23/02/21	Brett Rawson	26/02/2021
87	Electrostar Brief 01/21	Brett Rawson	26/02/2021
88	Depot Brief 01/21	Brett Rawson	26/02/2021
89	Covid Brief 23/02/21	Craig Back	26/02/2021
90	Depot Brief 01/21	Craig Back	26/02/2021
91	Electrostar Brief 01/21	Craig Back	26/02/2021
92	Electrostar Brief 01/21	Anthony Brace	27/02/2021
93	Depot Brief 01/21	Anthony Brace	27/02/2021
94	Covid Brief 23/02/21	Anthony Brace	27/02/2021
95	Electrostar Brief 01/21	Samuel Bennet	28/02/2021
96	Covid Brief 23/02/21	Ian Burt	28/02/2021
97	Depot Brief 01/21	Samuel Bennet	28/02/2021
98	Covid Brief 23/02/21	Samuel Bennet	28/02/2021
99	Covid Brief 23/02/21	Martin Allen	01/03/2021
100	Electrostar Brief 01/21	Martin Allen	01/03/2021
101	Depot Brief 01/21	Paul Hill	01/03/2021
102	Covid Brief 23/02/21	Paul Hill	01/03/2021
103	Depot Brief 01/21	Kevin Poole	01/03/2021
104	Depot Brief 01/21	Rory Dewey	01/03/2021
105	Depot Brief 01/21	alan Paul	01/03/2021
106	Depot Brief 01/21	Robert Hellings	01/03/2021
107	Covid Brief 23/02/21	Harry Showell	01/03/2021
108	Covid Brief 23/02/21	John Cosstick	01/03/2021
109	Depot Brief 01/21	Andrew Smalldridge	01/03/2021
110	Depot Brief 01/21	Raymond Burt	01/03/2021
111	Covid Brief 23/02/21	Dean Pennell	01/03/2021
112	Covid Brief 23/02/21	Gordon Price	01/03/2021
113	Depot Brief 01/21	Gordon Price	01/03/2021
114	Depot Brief 01/21	Dean Pennell	01/03/2021
115	Electrostar Brief 01/21	Dean Pennell	01/03/2021
116	Electrostar Brief 01/21	Gordon Price	01/03/2021
117	Electrostar Brief 01/21	Stephen Jordan	01/03/2021
118	Covid Brief 23/02/21	Stephen Jordan	01/03/2021
119	Depot Brief 01/21	Stephen Jordan	01/03/2021
120	Depot Brief 01/21	Daniel Robertson	01/03/2021
121	Covid Brief 23/02/21	Daniel Robertson	01/03/2021
122	Electrostar Brief 01/21	Daniel Robertson	01/03/2021
123	Covid Brief 23/02/21	Daniel Robertson	01/03/2021
124	Depot Brief 01/21	Daniel Robertson	01/03/2021
125	Depot Brief 01/21	Ryan Bower	01/03/2021
query

 

[Peter_SSs]

only a snap shot as kept saying too big.

We don't need (or want) a massive sample - just big enough to demonstrate the issue(s) without over-simplifying.
It is also a good idea to include the expected results too, but only needed here if my suggestion does not do what you want.

From your sample data, my formula (remember to adapt to the correct Table name in the formula) returned these results for employees who have not read "DM Brief- 01/21"

John McCuaig.xlsm
	I	J
1
2	DM Brief- 01/21	David Steadman
3		Toby Pielow
4		Richard Watson
5		Martin Mepham-Foster
6		David Richardson
7		Chris Farrant
8		Glen Harrison
9		Ryan Bower
10		Steff Jones
11		Kevin Leggett
12		Larry Franco
13		Martin Day
14		Daniel Barber
15		Katrina Rose Allen
16		Paul Bailey
17		Neil Edmunds
18		Richard Budd
19		Robert Soper
20		Fred Wells
21		Barry Lucas
22		Brett Rawson
23		Craig Back
24		Anthony Brace
25		Samuel Bennet
26		Ian Burt
27		Martin Allen
28		Paul Hill
29		Kevin Poole
30		Rory Dewey
31		alan Paul
32		Robert Hellings
33		Harry Showell
34		John Cosstick
35		Andrew Smalldridge
36		Dean Pennell
37		Gordon Price
38		Stephen Jordan
39		Daniel Robertson
40
Sheet2

Cell Formulas
Range	Formula
J2:J39	J2	=LET(Names,Table2[Employee Name],UNIQUE(FILTER(Names,ISNA(MATCH(Names,FILTER(Names,Table2[Title]=I2),0)))))
Dynamic array formulas.