Forum Discussion
Nesting SortBy and Filter functions
=FILTER(SORTBY(Worklist,List!A2:A500,1,List!G2:G500,1,List!E2:E500,1),List!I2:I500=0)Does this return the intended result in your sheet as well?
Currently i can't attach a sample file that's why i've added a screenshot that shows the output if the data is in the same sheet. The shared formula works across sheets in my sample file.
=FILTER(SORTBY(Worklist,List!A2:A500,1,List!G2:G500,1,List!E2:E500,1),List!I2:I500=0)Does this return the intended result in your sheet as well?
Currently i can't attach a sample file that's why i've added a screenshot that shows the output if the data is in the same sheet. The shared formula works across sheets in my sample file.
The original problem was that the lengths of the FILTERed array is different than the length for the SORTBY array(s).
Oliver, your solutions doesn't work because the SORTBY array is no longer in the same order as the array you are using to FILTER by. (i.e. the letter B is lined up with a number 2 so shouldn't be included in the output)
so you need to apply both operations to both sets. For example using Oliver's set-up try:
=LET(s, SORTBY(HSTACK(Worklist,List!I2:I500),List!A2:A500,1,List!G2:G500,1,List!E2:E500,1),
FILTER(TAKE(s,,1),TAKE(s,,-1)=0)
)