CODEWITHSUNDEEP
excelexcel-formula
Stefano
Stefano

Reputation: 179

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!

Upvotes: 1

Views: 12330

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60334

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)

Upvotes: 4

Related Questions