Search code examples
excelexcel-formula

Sortby function combined with Unique and filter functions: how to use it correclty?

I have the following table in excel, namely "table1":

CHECK City Country
Yes New York USA
Yes Barcelona Spain
No Rome Italy

The field "Check" is dynamically calculated with a formula

So far, I am using the following formula in another sheet to show only the Check= "Yes" rows:

=SORT(UNIQUE(FILTER(Table1;Table1[CHECK]="YES";""));3;1)

How can I sort by Country first, and then by the city in alphabetical order?

I tried the following but I get an erorr:

=SORTBY(UNIQUE(FILTER(Table1;Table1[CHECK]="YES";""));Table1[Country];1;Table1[City];1)

Any suggestion? It should be easy but I am struggling to figure out how to fix it.

Many thanks!

Solution

  • Your conceptual problem is that the array argument for the SORTBY function is not the original table; rather it is the array returned by the FILTER function.

    One method of using SORTBY for this issue would be:
    Be sure to replace the comma argument separators with semi-colon for your locale

    Note that we need to use the INDEX function to return the appropriate columns for SORTBY function.

    =LET(x, FILTER(TableX,TableX[CHECK]="Yes"),
          SORTBY(x,INDEX(x,0,3),1,INDEX(x,0,2),1))

    enter image description here

    Edit:
    If you don't have the LET function, you can just use the equivalent:

    =SORTBY(FILTER(TableX,TableX[CHECK]="Yes"),
INDEX(FILTER(TableX,TableX[CHECK]="Yes"),0,3),1,
INDEX(FILTER(TableX,TableX[CHECK]="Yes"),0,2),1)