Shift note Auditor automation

[Cambosity100]

Hi all in the Excel community. I have been auditing shift notes at work and have found massive problems with missing documents. I have to find out if staff have neglected to submit them , was the shift cancelled, public holiday? etc.... I use the sheet to cross reference against shift notes we have and shifts that have been paid for. Its working really well but the last part I have to do manually. Can anybody see a way to automate this last process. It would make the job immeasurably faster and more accurate. (people like Steve are easily over looked) All data and names are fictional.

Windows 10

Excel 365

With thanks in advance :)

[#2]

@Cambosity100 I believe you don't need any of the intermediate steps to arrive at the small table in columns U:V in Sheet1.

If you clean the Directory data by getting rid of empty rows, you can put the entire list with Date, Status and Notes, in preferably, a structured table and run a pivot table on that one. Demonstrated in Sheet2 in the attached file.