Abstract
Transcript
In this video, we’ll look at how to sort by multiple columns with the SORT and SORTBY functions.
In this worksheet, we have a list of names, projects, values, and regions. This data is not sorted.
Our goal is to sort the data first by region, then by name, and finally by value, with larger values first.
Let’s start with the SORTBY function. The first argument, array, is the data we want to sort, in this case, all data in the range B5:E16.
Now, the SORTBY function is designed to sort data by more than one dimension at the same time. A dimension can be a corresponding column, a range of cells, or an array.
Each sort dimension is entered as a pair of arguments: the array to sort by, and the sort direction.
For both SORT and SORTBY functions, sort_order is 1 for ascending order, and -1 for descending order.
In this example, we sort first by region, in ascending order.
Then we sort by name, also in ascending order.
Finally, we sort by amount, this time in descending order, so I need to provide a -1.
When I enter the formula, the data is sorted as needed.
Now let’s look at how to perform the same sort with the SORT function. Array is the same as before, the range B5:E16.
Now, at first glance, it doesn’t look like we can use SORT, because there is only one argument for sort index.
However, the trick is to use what's called an array constant. Inside curly brackets, we enter 4 for region, 1 for name, and 3 for amount.
Here we're specifying the columns we want to sort by.
To specify sort order, we use another array constant, with 1, for ascending, 1 for ascending, and -1 for descending.
When I enter the formula, you can see we get the same results that we got with the SORTBY function.
So, which function should you use?
In this case, it's a matter of personal preference. Either function works fine.
The SORTBY option is more typical, and probably a bit easier to understand for the average user.
The SORT function provides a more compact solution, but also more cryptic, since it depends on array constants.
Both functions are dynamic and will respond to any changes in the source data.
