I'm attempting to set up a filter in cell U3 that will extract the staff from the left hand table that are available after a specfic date specified in cell S2. For clarity, each member of staff is assigned to different projects with a start and end date - this is why they have multiple entries in the table.
I have created the following filter in cell U3 with the aim of extracting the available staff after the date 01/11/2022 as denoted in cell S2 (please see image) =FILTER(FILTER(AllStaffProjectAllocationTbl,AllStaffProjectAllocationTbl[End Date]>$S$2), {1,1,0,0,0,0,1,0,0,0})
As you can see, the result of the filter is showing me the 3 people still allocated to a project after the 01/11/22, whereas my hope is to have the following list :
Employee End Date
Peter 30/06/2022
Richard 30/09/2020
Chris 31/05/2021
Roger 30/09/2022
Wesley 31/08/2021
Any ideas on what I'd need to change to extract the above data would be appreciated?
CodePudding user response：
You can use this formula:
=LET(uniqueEmployees,UNIQUE(data[Employee]), maxDatePerEmployee,BYROW(uniqueEmployees,LAMBDA(e,MAX(FILTER(data[End Date],data[Employee]=e)))), EmployeesWithMaxDate,CHOOSE({1,2},uniqueEmployees,maxDatePerEmployee), FILTER(EmployeesWithMaxDate,maxDatePerEmployee<=E1))
uniqueEmployeesreturns a list of unqiue namesmaxDatePerEmployeereturns per unique employee the latest date- using
choosewe build the new matrix - which then gets filtered by the available date - all employees whose latest end date is before the available date are returned.
