The SORTBY function is useful in cases where you want to sort a range or array by some key column or row that is not part of the array or range. This function is thus more flexible than the SORT function, as the "by_array" can be any range or array of the appropriate size.
You can find the examples in this blog for SORTBY in this Excel workbook here.
SORYBY Syntax
| SORTBY(array,by array1,[order array1] | |
|---|---|
| array | Array or range to be sorted (required). |
| by array1 | array or range of first sort key (required). |
| order array1 | Optional -1 for descending (default is 1, ascending). |
| by array2 | Optional array or range of second sort key |
| order array2 | -1 for descending, 1 for ascending (required if by array2 specified). |
| This function can accept additional arguments in pairs, specifying the next sort key range and order. | |
The examples below use this SoryByData table:
The first example sorts on the ProductName column in the default ascending order:
=SORTBY(SortByData,SortByData[ProductName])
Note that the above result in J14:O24 is formatted to appear like a table, but is not actually a table, as dynamic array formulas and spilling behavior are not supported in table cells. Instead, the range is formatted to appear like a table and the table header cells J13:O13 are copied from the original table header cells B13:G13.
The next example sorts on the Quantity in descending order:
=SORTBY(SortByData,SortByData[Quantity],-1)
The last example sorts on the SalesPerson in ascending order, then Quantity in descending order:
=SORTBY(SortByData,SortByData[SalesPerson],1,SortByData[Quantity],-1)
Note that instead of specifying column or row indices in the range for sort keys, when using the SORTBY function you can instead use structured table references to the entire table column, which is much clearer and easier to read than the formulas using the SORT function.
Also note that the specified array or range of values does not need to be part of the sort data, it only needs to have the correct dimensions and specify a set of values for sorting, which can be calculated values. Later blogs will explore this more using other dynamic array functions with SORTBY.
SORTBY in GrapeCity Products
The SORTBY function is supported in these GrapeCity spreadsheet controls and components:
-
Spread.NET Windows Forms Spreadsheet Control | Demo
-
SpreadJS Javascript/Typescript/Angular/React/Vue Spreadsheet Widget | Demo
-
GrapeCity Documents for Excel .NET and Java Controls | .NET Demo | Java Demo
Sean Lawyer