Forum Discussion
Need help with SORTBY function.
Hi all,
In reference to this formula, SORT(FILTER('Big 4 NOV 21-23'!A3:D221,'Big 4 NOV 21-23'!B3:B221="M"),3,-1) I can only sort using one column (3).
I need to sort the rows by column 3 then column 4. Column 5 is static
I have tried using the SORTBY function, but there is an #VALUE issue with the array.
Appreciate any help with this!
Cheers Richard.
| NAME | MENS | TOTAL UNITS | TOTAL WEIGHT | POINTS |
| Fred | M | 4 | 8.653 | 25 |
| Rob | M | 4 | 4.556 | 20 |
| Bob | M | 4 | 8.517 | 17 |
| Rick | M | 4 | 10.584 | 15 |
| Dave | M | 4 | 10.425 | 14 |
| Greg | M | 4 | 8.773 | 13 |
| Cal | M | 3 | 5.958 | 12 |
| Ed | M | 3 | 3.776 | 11 |
| Hal | M | 2 | 3.834 | 10 |
| Ian | M | 2 | 2.324 | 9 |
| Joe | M | 2 | 5.543 | 8 |
| Ken | M | 2 | 2.751 | 7 |
| Len | M | 2 | 7.179 | 6 |
| Neil | M | 2 | 13.077 | 5 |
| Mike | M | 1 | 4.772 | 4 |
| Ollie | M | 1 | 5.983 | 3 |
You do not need SORTBY() in this case. You can specify multi column for sorting and individual sort order for each column.
=SORT(FILTER('Big 4 NOV 21-23'!A3:E221,'Big 4 NOV 21-23'!B3:B221="M"),{3,4,5},{-1,-1,-1})
5 Replies
You do not need SORTBY() in this case. You can specify multi column for sorting and individual sort order for each column.
=SORT(FILTER('Big 4 NOV 21-23'!A3:E221,'Big 4 NOV 21-23'!B3:B221="M"),{3,4,5},{-1,-1,-1})that is probably because the sortby() function needs an array for the sortby parameters (not just a column number). So you need to filter the table then pass the full column 3, column 4, and column 5 to the sortby parts. this is perfect time to use the LET() statement:
=LET(in, A1:E17, a, FILTER(in,CHOOSECOLS(in,2)="M"), VSTACK( TAKE(in,1), SORTBY(a,CHOOSECOLS(a,3),-1,CHOOSECOLS(a,4),-1,CHOOSECOLS(a,5),-1)) )so you can pick whatever range you want to use for line 1
line 2 performs the FILTER (in this case filter the second column for "M"
line 3 is the output and on line 4 it will return the header row and line 5 does the sort
notice on line 5 each sortby is passing the full column of corresponding data.
Hi m_tarler,
Thanks for taking the time to reply to my question. I'm sure your solution would work perfectly, but i have no experience with some of the functions you recommended.
Cheers Richard.
