The SORT function provides a more compact solution, but also more cryptic, since it depends on array constants.īoth functions are dynamic and will respond to any changes in the source data. The SORTBY option is more typical, and probably a bit easier to understand for the average user. In this case, it's a matter of personal preference. When I enter the formula, you can see we get the same results that we got with the SORTBY function. ![]() To specify sort order, we use another array constant, with 1, for ascending, 1 for ascending, and -1 for descending. Here we're specifying the columns we want to sort by. Inside curly brackets, we enter 4 for region, 1 for name, and 3 for amount. However, the trick is to use what's called an array constant. The following code sorts a multi-column range named Fruits by the data in the first column, and then by the data in the second column. Now, at first glance, it doesn’t look like we can use SORT, because there is only one argument for sort index. Applies to: Visual Studio Visual Studio for Mac Visual Studio Code You can sort data that is contained in worksheet ranges and lists at run time. Array is the same as before, the range B5:E16. Now let’s look at how to perform the same sort with the SORT function. When I enter the formula, the data is sorted as needed. Then we sort by name, also in ascending order.įinally, we sort by amount, this time in descending order, so I need to provide a -1. In this example, we sort first by region, in ascending order. A dimension can be a corresponding column, a range of cells, or an array.Įach sort dimension is entered as a pair of arguments: the array to sort by, and the sort direction.įor both SORT and SORTBY functions, sort_order is 1 for ascending order, and -1 for descending order. Now, the SORTBY function is designed to sort data by more than one dimension at the same time. ![]() The first argument, array, is the data we want to sort, in this case, all data in the range B5:E16. ![]() Our goal is to sort the data first by region, then by name, and finally by value, with larger values first. In this worksheet, we have a list of names, projects, values, and regions. In this video, we’ll look at how to sort by multiple columns with the SORT and SORTBY functions.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |