Search code examples
excelexcel-formulaletexcel-lambda

Including additional table columns into Let function constructed table with the ability to sort based on column value Excel

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
Solution

  • 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.