This function sorts the data in a cell range or an array. The cell contents are extracted from the source array, the data is sorted and the results spill into the resultant range with a dynamic array of values arranged in ascending or descending order. Users can sort the values by one or more columns in the spreadsheet as per custom requirements.
The SORT function requires the sort keys to be included inside the specified array. Further, users can perform sorting on multiple fields by executing the multiple-key sorting operation on the worksheet. For instance - let's say you have a large database that you want to sort in such a way that you can obtain the sales data to analyse which region sells how many product units. Now, in this scenario, you will have to perform sorting on two fields concurrently - 1) Sorting based on the Region column (that specifies the area where the product is being sold) and 2) Sorting based on the Sales column (that contains the figures as to how many products are sold). In multiple-key sorting or multi-level data sorting, the sort index and the sort order can be of the same length which specifies multiple sort keys and their sort key orders respectively.
SORT (array, [sort_index], [sort_order], [by_col])
SORT function has the following arguments:
| Argument | Description |
|---|---|
| array | [required] Specifies the range or array that you want to sort. |
| sort_index | [optional] Specifies the column index of the row or column to sort by. If nothing is specified, the default value 1 is used. |
| sort_order | [optional] Specifies the sort order. The value 1 indicates ascending order and the value -1 indicates descending order. The default value is 1 i.e. ascending. |
| by_col | [optional] If this argument is TRUE, it refers to the "sort by column" operation and if FALSE, it refers to the "sort by row" operation. The default value is Boolean FALSE i.e. the sort by row operation. |
If you are implementing multiple-key sorting, the arguments sort_index [specifies multiple sort keys] and the sort_order [specifies the sort key orders] in the above table can be of the same length.
Accepts a range or array that users want to sort. Returns a sorted array.
For instance - The cell D4 in the following image contains the formula "=SORT(A4:A15)" and returns the customer names sorted by age in the increasing order.
If you want to sort all the unique values in the range A4 to A15, you can either apply the sort function on the unique list displayed in the column C4 or you can also combine both the functions SORT and UNIQUE into a single formula.
For instance, the cell E4 in the following image contains the formula "=SORT(C4#)" where # indicates a list. This formula will sort the list of values in column C (where cell C4 already contains the UNIQUE formula "=UNIQUE(A4:A15)") and displays the results in column E.
Alternatively, you can also combine both the functions SORT and UNIQUE. For instance, the cell F4 in the following image contains the formula "=SORT(UNIQUE(A4:A15))" which returns all the unique values in the range A4:A15 sorted alphabetically.
This function is available in Spread for Windows Forms 12.1 or later.
