Abstract
Transcript
In this video, we’ll look at a basic example of sorting with the SORTBY function.
In this worksheet, we have a list of names, scores, and groups. Currently, the data is not sorted.
Our goal is to sort this data by group, then by score in descending order.
I’ll start off by placing the cursor in cell F5, then typing an equals sign (=) and the first few letters of “SORTBY”. Once we have a match, I’ll press the TAB key to complete.
The first argument is called array. This is the data we want to sort. In this case, that’s the range B5:D14.
Next, we need to enter a pair of arguments. With the SORTBY function, you supply sort order in pairs of inputs. Each pair has an argument for range, and an argument for sort order.
Since we want to sort by Group first, the range is D5:D14. For sort_order1, we use 1, because we want to sort in ascending order.
Now, if I stop there and enter the formula, notice we now have the data sorted by group. Inside each group, the sort order is unchanged, and reflects the order that the names appear in the source data.
To finish the problem, and sort the data by group, and then by score, I need to add another pair of arguments.
For by_array2, I’ll use the scores in C5:C14. For sort_order2, I’ll use -1, since we want to sort in descending order.
Now when I enter the formula, the data is sorted first by group, and then by score, with highest scores appearing first.
Depending on your needs, you can add more arguments to the SORTBY function to extend the sort operation to other columns.
Finally, note that sort_order defaults to 1, which specifies ascending order. This means I could remove the first sort_order argument.
Personally, however, I like to provide values for optional arguments as a reminder to myself of the behavior I'm expecting.
