On Saturday, February 12, 2022 at 10:51:58 AM UTC+2, Håkan Björkström wrote:
I have tried to filter a range by both rows and columns. The columns are filtered by choosing columns with function CHOOSE({1,2,3,4... and this works perfect. I need to sort the result by a specific column contained in the filter, but the SORTBY
returns #VALUE The funny thing is that I am able to SORTBY the SPILL range, but not SORTBY combined with the filter. I can combine SORT and FILTER but not SORTBY and FILTER. Is there another way to sort the columns which would allow using SORTBY(FILTER...
?
My conclusion regarding SORTBY and FILTER:
Rather often you want to extract rows and sort on a column information in an Excel table. At the first sight I was excited when I found Excel 365 comes with new functions FILTER, SORT and SORTBY, Yes, they ARE very useful.
After hours of collaborating with a combination of SORTBY an FILTER (extract a set of customer names from a table and sort the result on ZIP code), I found that it is impossible. Why? FILTER output contains fewer rows than SORTBY column, and thus the
function combination fails.
What if I swap the functions by entering FILTER(SORTBY(...? Yes, FILTER function does not care about number of rows in the sortby output, but FILTER tries to filter the sorted array based on an unsorted array, which will result in a wrong result.
Workaround:
With my example (filter customer names and sort on ZIP code), you can replace SORTBY by SORT. The inner function contains two nested FILTER clauses; one clause filters the rows based on your need, while the other filters/rearranges the columns putting
the SORTBY-column to the leftmost column. After that you can simply use SORT, which automatically sorts by the first column.
Perhaps there are more and simply solutions. Please let me/us know how you solve this actual problem.
/Håkan
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)