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

John McCuaig

New Member
Joined
Apr 11, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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.

1618124869580.png
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,535
Office Version
  1. 365
Platform
  1. Windows
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
ABHIJ
1TitleEmployee NameNot read by
2Title 1Emp11Title 1Emp10
3Title 2Emp12Emp7
4Title 3Emp11Emp2
5Title 1Emp17Emp5
6Title 5Emp10Emp3
7Title 1Emp6
8Title 1Emp12
9Title 3Emp7
10Title 4Emp2
11Title 2Emp10
12Title 1Emp11
13Title 2Emp5
14Title 1Emp16
15Title 4Emp3
16Title 5Emp3
17
Sheet1
Cell Formulas
RangeFormula
J2:J6J2=LET(Names,Table1[Employee Name],UNIQUE(FILTER(Names,ISNA(MATCH(Names,FILTER(Names,Table1[Title]=I2),0)))))
Dynamic array formulas.
 

John McCuaig

New Member
Joined
Apr 11, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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
ABC
1TitleEmployee NameDate Stamp
2DM Brief- 01/21John McCuaig15/02/2021
3COVID-01/21 C19 BriefJohn McCuaig15/02/2021
4PERFORMANCE 01/21John McCuaig15/02/2021
5DM Brief- 01/21John McCuaig15/02/2021
6COVID-01/21 C19 BriefJohn McCuaig15/02/2021
7SAFETY Performance 01/21Nick De jersey15/02/2021
8COVID-01/21 C19 BriefJohn McCuaig15/02/2021
9PERFORMANCE 01/21John McCuaig15/02/2021
10SAFETY Performance 01/21John McCuaig15/02/2021
11PERFORMANCE 02/21John McCuaig15/02/2021
12PERFORMANCE 01/21John McCuaig15/02/2021
13PERFORMANCE 01/21David Steadman15/02/2021
14PERFORMANCE 02/21David Steadman15/02/2021
15PERFORMANCE 01/21David Steadman15/02/2021
16COVID-01/21 C19 BriefRaymond Burt15/02/2021
17DM Brief- 01/21Raymond Burt15/02/2021
18SAFETY Performance 01/21David Steadman15/02/2021
19PERFORMANCE 01/21Raymond Burt15/02/2021
20COVID-01/21 C19 BriefToby Pielow16/02/2021
21PERFORMANCE 01/21Nick De jersey16/02/2021
22DM Brief- 01/21Nick De jersey16/02/2021
23DM Brief- 01/21Nick De jersey16/02/2021
24COVID-01/21 C19 BriefRichard Watson16/02/2021
25COVID-01/21 C19 BriefNick De jersey17/02/2021
26PERFORMANCE 01/21Nick De jersey17/02/2021
27PERFORMANCE 02/21Nick De jersey17/02/2021
28SAFETY Performance 01/21Nick De jersey17/02/2021
29COVID-01/21 C19 BriefMartin Mepham-Foster18/02/2021
30COVID-01/21 C19 BriefJohn McCuaig22/02/2021
31COVID-01/21 C19 BriefDavid Richardson23/02/2021
32Depot Brief 01/21John McCuaig24/02/2021
33Electrostar Brief 01/21John McCuaig24/02/2021
34Covid Brief 23/02/21John McCuaig24/02/2021
35Depot Brief 01/21John McCuaig24/02/2021
36Electrostar Brief 01/21John McCuaig24/02/2021
37Covid Brief 23/02/21John McCuaig24/02/2021
38Covid Brief 23/02/21Chris Farrant24/02/2021
39Depot Brief 01/21Chris Farrant24/02/2021
40Electrostar Brief 01/21Chris Farrant24/02/2021
41Covid Brief 23/02/21Glen Harrison24/02/2021
42Depot Brief 01/21Glen Harrison24/02/2021
43Electrostar Brief 01/21Glen Harrison24/02/2021
44Depot Brief 01/21Ryan Bower24/02/2021
45Covid Brief 23/02/21Ryan Bower24/02/2021
46Electrostar Brief 01/21Ryan Bower24/02/2021
47Covid Brief 23/02/21Richard Watson24/02/2021
48Electrostar Brief 01/21Richard Watson24/02/2021
49Depot Brief 01/21Richard Watson24/02/2021
50Electrostar Brief 01/21Steff Jones24/02/2021
51Depot Brief 01/21Steff Jones24/02/2021
52Covid Brief 23/02/21Steff Jones24/02/2021
53Covid Brief 23/02/21Kevin Leggett25/02/2021
54Covid Brief 23/02/21Larry Franco25/02/2021
55Depot Brief 01/21Martin Day25/02/2021
56Covid Brief 23/02/21Daniel Barber25/02/2021
57Electrostar Brief 01/21Nick De jersey25/02/2021
58Electrostar Brief 01/21Nick De jersey25/02/2021
59Covid Brief 23/02/21Nick De jersey25/02/2021
60Covid Brief 23/02/21Nick De jersey25/02/2021
61Electrostar Brief 01/21Nick De jersey25/02/2021
62Covid Brief 23/02/21Katrina Rose Allen25/02/2021
63Depot Brief 01/21Katrina Rose Allen25/02/2021
64Electrostar Brief 01/21Katrina Rose Allen25/02/2021
65Covid Brief 23/02/21Paul Bailey25/02/2021
66Covid Brief 23/02/21Nick De jersey25/02/2021
67Depot Brief 01/21Nick De jersey25/02/2021
68Covid Brief 23/02/21Neil Edmunds25/02/2021
69Depot Brief 01/21Neil Edmunds25/02/2021
70Electrostar Brief 01/21Neil Edmunds25/02/2021
71Covid Brief 23/02/21Richard Budd25/02/2021
72Depot Brief 01/21Richard Budd25/02/2021
73Electrostar Brief 01/21Richard Budd25/02/2021
74Electrostar Brief 01/21Robert Soper26/02/2021
75Depot Brief 01/21Robert Soper26/02/2021
76Covid Brief 23/02/21Robert Soper26/02/2021
77Covid Brief 23/02/21Fred Wells26/02/2021
78Electrostar Brief 01/21Fred Wells26/02/2021
79Depot Brief 01/21Fred Wells26/02/2021
80Covid Brief 23/02/21Barry Lucas26/02/2021
81Depot Brief 01/21David Steadman26/02/2021
82Covid Brief 23/02/21David Steadman26/02/2021
83Electrostar Brief 01/21David Steadman26/02/2021
84Covid Brief 23/02/21Nick De jersey26/02/2021
85Depot Brief 01/21Nick De jersey26/02/2021
86Covid Brief 23/02/21Brett Rawson26/02/2021
87Electrostar Brief 01/21Brett Rawson26/02/2021
88Depot Brief 01/21Brett Rawson26/02/2021
89Covid Brief 23/02/21Craig Back26/02/2021
90Depot Brief 01/21Craig Back26/02/2021
91Electrostar Brief 01/21Craig Back26/02/2021
92Electrostar Brief 01/21Anthony Brace27/02/2021
93Depot Brief 01/21Anthony Brace27/02/2021
94Covid Brief 23/02/21Anthony Brace27/02/2021
95Electrostar Brief 01/21Samuel Bennet28/02/2021
96Covid Brief 23/02/21Ian Burt28/02/2021
97Depot Brief 01/21Samuel Bennet28/02/2021
98Covid Brief 23/02/21Samuel Bennet28/02/2021
99Covid Brief 23/02/21Martin Allen01/03/2021
100Electrostar Brief 01/21Martin Allen01/03/2021
101Depot Brief 01/21Paul Hill01/03/2021
102Covid Brief 23/02/21Paul Hill01/03/2021
103Depot Brief 01/21Kevin Poole01/03/2021
104Depot Brief 01/21Rory Dewey01/03/2021
105Depot Brief 01/21alan Paul01/03/2021
106Depot Brief 01/21Robert Hellings01/03/2021
107Covid Brief 23/02/21Harry Showell01/03/2021
108Covid Brief 23/02/21John Cosstick01/03/2021
109Depot Brief 01/21Andrew Smalldridge01/03/2021
110Depot Brief 01/21Raymond Burt01/03/2021
111Covid Brief 23/02/21Dean Pennell01/03/2021
112Covid Brief 23/02/21Gordon Price01/03/2021
113Depot Brief 01/21Gordon Price01/03/2021
114Depot Brief 01/21Dean Pennell01/03/2021
115Electrostar Brief 01/21Dean Pennell01/03/2021
116Electrostar Brief 01/21Gordon Price01/03/2021
117Electrostar Brief 01/21Stephen Jordan01/03/2021
118Covid Brief 23/02/21Stephen Jordan01/03/2021
119Depot Brief 01/21Stephen Jordan01/03/2021
120Depot Brief 01/21Daniel Robertson01/03/2021
121Covid Brief 23/02/21Daniel Robertson01/03/2021
122Electrostar Brief 01/21Daniel Robertson01/03/2021
123Covid Brief 23/02/21Daniel Robertson01/03/2021
124Depot Brief 01/21Daniel Robertson01/03/2021
125Depot Brief 01/21Ryan Bower01/03/2021
query
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,535
Office Version
  1. 365
Platform
  1. Windows
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
IJ
1
2DM Brief- 01/21David Steadman
3Toby Pielow
4Richard Watson
5Martin Mepham-Foster
6David Richardson
7Chris Farrant
8Glen Harrison
9Ryan Bower
10Steff Jones
11Kevin Leggett
12Larry Franco
13Martin Day
14Daniel Barber
15Katrina Rose Allen
16Paul Bailey
17Neil Edmunds
18Richard Budd
19Robert Soper
20Fred Wells
21Barry Lucas
22Brett Rawson
23Craig Back
24Anthony Brace
25Samuel Bennet
26Ian Burt
27Martin Allen
28Paul Hill
29Kevin Poole
30Rory Dewey
31alan Paul
32Robert Hellings
33Harry Showell
34John Cosstick
35Andrew Smalldridge
36Dean Pennell
37Gordon Price
38Stephen Jordan
39Daniel Robertson
40
Sheet2
Cell Formulas
RangeFormula
J2:J39J2=LET(Names,Table2[Employee Name],UNIQUE(FILTER(Names,ISNA(MATCH(Names,FILTER(Names,Table2[Title]=I2),0)))))
Dynamic array formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,455
Members
417,025
Latest member
MusterDuster

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