1

I have the following function giving a list of available staff after a specified date set in T2

=LET(uniqueEmployees,UNIQUE(AllStaffProjectAllocationTbl[Employee]), maxDatePerEmployee,BYROW(uniqueEmployees,LAMBDA(e,MAX(FILTER(AllStaffProjectAllocationTbl[End Date],AllStaffProjectAllocationTbl[Employee]=e)))), EmployeesWithMaxDate,CHOOSE({1,2},uniqueEmployees,maxDatePerEmployee), FILTER(EmployeesWithMaxDate,maxDatePerEmployee<=T2))

Is there a way to include the role and discipline into the right hand table and additionally, is there a way to set this up so I can filter that new table by discipline or role?

Additional Columns and Sorting

All the data from the left most table AllStaffProjectAllocationTbl

Employee    Role    Discipline  Project Name    Start Date  Start Year  End Date
Bob Senior Programmer   Programming Project 1   01/01/2020  2020    28/02/2020
Bob Senior Programmer   Programming Project 2   01/03/2020  2020    31/03/2020
Bob Senior Programmer   Programming Project 3   01/04/2020  2020    30/06/2020
Dave    Mid Level Programmer    Programming Project 1   01/02/2020  2020    28/02/2020
Dave    Mid Level Programmer    Programming Project 3   01/03/2020  2020    31/07/2020
Peter   Senior Programmer   Programming Project 1   01/01/2020  2020    31/01/2020
Peter   Senior Programmer   Programming Project 2   01/04/2020  2020    31/05/2020
Peter   Senior Programmer   Programming Project 3   01/06/2020  2020    30/06/2020
Jack    Junior Programmer   Programming Project 1   01/02/2020  2020    30/06/2020
Richard Senior Artist   Art Project 1   01/03/2020  2020    30/04/2020
Richard Senior Artist   Art Project 2   01/05/2020  2020    30/09/2020
Rodney  Lead QA QA  Project 1   01/03/2020  2020    30/06/2020
Chris   Senior Producer Production  Project 1   01/01/2020  2020    30/08/2020
Roger   QA  QA  Project 1   01/01/2020  2020    30/04/2020
Roger   QA  QA  Project 2   01/05/2020  2020    31/05/2020
Roger   QA  QA  Project 3   01/06/2020  2020    30/06/2020
Wesley  Mid Level Programmer    Programming Project 1   01/02/2020  2020    31/05/2020
Wesley  Mid Level Programmer    Programming Project 2   01/06/2020  2020    31/07/2020
ZygD
  • 22,092
  • 39
  • 79
  • 102
Automation Monkey
  • 355
  • 1
  • 8

1 Answers1

1

The following formula should work:

=LET(LastRow,MAX(ROW($A:$A)*NOT(ISBLANK($A:$A))),IFERROR(INDEX(FILTER(OFFSET($A:$H,1,0,LastRow-1),(OFFSET($H:$H,1,0,LastRow-1)<$T$2)*(TRANSPOSE(MMULT(SEQUENCE(1,LastRow-1,1,0),TRANSPOSE(OFFSET($H:$H,1,0,LastRow-1)>TRANSPOSE(OFFSET($H:$H,1,0,LastRow-1)))*(OFFSET($A:$A,1,0,LastRow-1)=TRANSPOSE(OFFSET($A:$A,1,0,LastRow-1)))*(1)))-MMULT((TRANSPOSE(OFFSET($A:$A,1,0,LastRow-1))=OFFSET($A:$A,1,0,LastRow-1))+0,SEQUENCE(LastRow-1,1,1,0))+1=0)),SEQUENCE(ROWS(UNIQUE($A:$A,0,0))-2),{1,3,8}),""))

You can select the columns that you want to include at the very end of the formula in the curly brackets. Here I selected {1,3,8}

Edit 1

Here the formula again with the ability to sort:

=LET(LastRow,MAX(ROW($A:$A)*NOT(ISBLANK($A:$A))),IFERROR(SORT(INDEX(FILTER(OFFSET($A:$H,1,0,LastRow-1),(OFFSET($H:$H,1,0,LastRow-1)<$T$2)*(TRANSPOSE(MMULT(SEQUENCE(1,LastRow-1,1,0),TRANSPOSE(OFFSET($H:$H,1,0,LastRow-1)>TRANSPOSE(OFFSET($H:$H,1,0,LastRow-1)))*(OFFSET($A:$A,1,0,LastRow-1)=TRANSPOSE(OFFSET($A:$A,1,0,LastRow-1)))*(1)))-MMULT((TRANSPOSE(OFFSET($A:$A,1,0,LastRow-1))=OFFSET($A:$A,1,0,LastRow-1))+0,SEQUENCE(LastRow-1,1,1,0))+1=0)),SEQUENCE(ROWS(UNIQUE($A:$A,0,0))-2),{1,3,8}),3),""))

At the end of the formula, you first select the columns from the data array within the curly brackets (here {1,3,8}) and then you select the column by which to sort the reduced data (here 3). Note that the 3rd column selected for sorting is the 8th column of the original data array since it has been reduced to column 1, 3 and 8.

Edit 2

Here is the formula using only the named range 'AllStaffProjectAllocationTbl' and the 'Available From' value in T2 as input:

=LET(LastRow,MAX(ROW(AllStaffProjectAllocationTbl)*NOT(ISBLANK(AllStaffProjectAllocationTbl))),EmployeeName,OFFSET(AllStaffProjectAllocationTbl,1,0,LastRow-1,1),EndDate,OFFSET(AllStaffProjectAllocationTbl,1,7,LastRow-1,1),IFERROR(SORT(INDEX(FILTER(OFFSET(AllStaffProjectAllocationTbl,1,0,LastRow-1),(EndDate<$T$2)*(TRANSPOSE(MMULT(SEQUENCE(1,LastRow-1,1,0),TRANSPOSE(EndDate>TRANSPOSE(EndDate))*(EmployeeName=TRANSPOSE(EmployeeName))*(1)))-MMULT((TRANSPOSE(EmployeeName)=EmployeeName)+0,SEQUENCE(LastRow-1,1,1,0))+1=0)),SEQUENCE(ROWS(UNIQUE(EmployeeName,0,0))),{1,3,8}),3),""))

At the beginning of the formula, the LET() function defines the names 'EmployeeName' and 'EndDate' where the prior is assumed to be in column 1 of the named range and the latter to be in column 8 of the named range. Should that change, the column index in the OFFSET() function will have to be adjusted accordingly.

The selection of output columns and sorting column is the same as in the last edit

Edit 3

Here with addressing the data table using structured references, e.g. MyData[Header 1]

=LET(AvailabilityCalc,FILTER(AllStaffProjectAllocationTbl,(AllStaffProjectAllocationTbl[End Date]<$T$2)*(TRANSPOSE(MMULT(SEQUENCE(1,ROWS(AllStaffProjectAllocationTbl),1,0),TRANSPOSE(AllStaffProjectAllocationTbl[End Date]>TRANSPOSE(AllStaffProjectAllocationTbl[End Date]))*(AllStaffProjectAllocationTbl[Employee]=TRANSPOSE(AllStaffProjectAllocationTbl[Employee]))*(1)))-MMULT((TRANSPOSE(AllStaffProjectAllocationTbl[Employee])=AllStaffProjectAllocationTbl[Employee])+0,SEQUENCE(ROWS(AllStaffProjectAllocationTbl),1,1,0))+1=0)),IFERROR(SORT(INDEX(AvailabilityCalc,SEQUENCE(ROWS(AvailabilityCalc)),{1,3,8}),3),""))

Where the relevant data table is named 'AllStaffProjectAllocationTbl' and the therein addressed headers are named 'Employee' and 'End Date'

Edit 4

As asked by comment below:

=LET(AvailabilityCalc,FILTER(AllStaffProjectAllocationTbl,    IF(ISBLANK($W$2),1,(AllStaffProjectAllocationTbl[Role]=$W$2))*IF(ISBLANK($V$2),1,(AllStaffProjectAllocationTbl[Employee]=$V$2))*IF(ISBLANK($X$2),1,(AllStaffProjectAllocationTbl[Discipline]=$X$2))*   (AllStaffProjectAllocationTbl[End Date]<$Y$2)*(TRANSPOSE(MMULT(SEQUENCE(1,ROWS(AllStaffProjectAllocationTbl),1,0),TRANSPOSE(AllStaffProjectAllocationTbl[End Date]>TRANSPOSE(AllStaffProjectAllocationTbl[End Date]))*(AllStaffProjectAllocationTbl[Employee]=TRANSPOSE(AllStaffProjectAllocationTbl[Employee]))*(1)))-MMULT((TRANSPOSE(AllStaffProjectAllocationTbl[Employee])=AllStaffProjectAllocationTbl[Employee])+0,SEQUENCE(ROWS(AllStaffProjectAllocationTbl),1,1,0))+1=0)),IFERROR(SORT(INDEX(AvailabilityCalc,SEQUENCE(ROWS(AvailabilityCalc)),{1,2,3,8}),3),""))

I added some spaces to highlight the added section for further filtering. Those spaces do not compromise the functionality of the formula.

Dattel Klauber
  • 800
  • 1
  • 2
  • 17
  • Hey Dattel, works great. Is there a way this can reference the AllStaffProjectAllocationTbl instead of it running through the whole columns ( I have other data underneath)? – Automation Monkey Aug 30 '22 at 12:43
  • What exactly is the range of AllStaffProjectAllocationTbl? Is it column A2:H19 in the screenshot above or does it start with row 1? – Dattel Klauber Aug 30 '22 at 12:48
  • The data for the table is currently $A$1:$K$19 with headers on row 1. The other reason I'd like to use the table is I'd like to move the formula in to a seperate sheet for calculating the available staff. – Automation Monkey Aug 30 '22 at 12:55
  • Adjustment implemented via edit in the answer above – Dattel Klauber Aug 30 '22 at 13:16
  • Hey Dattel, sort on the date is great thanks. Is it possible to change the ranges to AllStaffProjectAllocationTbl[Employee] etc? I attempted this but got some errors. – Automation Monkey Aug 30 '22 at 14:01
  • Additionally, is it possible to then convert it to a table such that it can be filtered by the discipline or Role type? The data now looks like this - I added the role too - https://imgur.com/tyLfnVe And when I try to convert it to a table I get a spill error when selecting the following to create the table - https://imgur.com/OMbavCs – Automation Monkey Aug 30 '22 at 14:14
  • See adjustment to your previous comment in Edit 3. – Dattel Klauber Aug 30 '22 at 14:35
  • Regarding filtering, the formula uses the FILTER() function. Simply add more criteria to it through multiplication. E.g. by adding the following line of formula in the FILTER: `...IF(ISBLANK($T$5),1,(AllStaffProjectAllocationTbl[Discipline]=$T$5)+0)*...` where it is assumed that the 'Discipline' value to filter by is given in cell T5: https://i.stack.imgur.com/uWRzt.png – Dattel Klauber Aug 30 '22 at 14:44
  • Here is an example of how you could filter for each of the columns via formula: https://i.stack.imgur.com/1ivtd.png (Note: my table is named 'MyDataRange' here) – Dattel Klauber Aug 30 '22 at 14:54
  • Hi Dattel, This looks great, although I can't quite see what you've done with the formula at the beginning with the red line. Would you mind sending over the full formula please? – Automation Monkey Aug 30 '22 at 20:17
  • Yeah ofc. The formula is exceeding the character limit for comments here so I'll paste it as Edit 4 to the answer above. Make sure to accept the answer when completed. – Dattel Klauber Aug 30 '22 at 22:13
  • Thanks Dattel, really appreciate all the help. Great solution that has not only solved my question but has provided more functionality than I was initially thinking of. – Automation Monkey Aug 31 '22 at 09:10
  • Hi Dattel, I realised I wasn't including staff that are not allocated to a project who live in a table called StaffDetailsTbl. It has their name, whether they are part of the development team, their start employment date and end employment date (if they leave us). Is it possible to include those marked as development staff that are not on the AllStaffProjectAllocationTbl and the Available From date is within their employment start and end date (if not blank)? – Automation Monkey Sep 14 '22 at 10:50