Including additional table columns into Let function constructed table with the ability to sort base-CodePudding

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?

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

CodePudding user response：

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.