Documentation/Calc Functions/SORTBY
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Function name:
SORTBY
Category:
Spreadsheet
Summary:
The SORTBY function sorts the contents of an array based on the values from a vector. It allows multi-level sorting.
The SORTBY function sorts rows or columns, depending on the orientation of the vector. The vector does not need to be part of the to be sorted array and need not even be stored on the sheet but may be result of functions.
Syntax:
SORTBY( DataSource; ByVector_1[; SortOrder_1 = 1][; ByVector_2[; SortOrder_2 = 1]]…[; ByVector_N[; SortOrder_N = 1]] )
Returns:
The result from SORTBY is a sorted copy of the DataSource.
Arguments:
DataSource (required) is the to be sorted array.
ByVector_1 (required) is ordered and this process of ordering is applied to the DataSource records.
SortOrder_1 … SortOrder_N (optional) The value 1 specifies ascending order, value -1 specifies descending order. Default value is 1. Other values than 1 or -1 are not allowed.
ByVector_2 … ByVector_N (optional) specify additional levels of sorting. Data records with same value in ByVector_1 … ByVector_K are ordered according ByVector_K+1 and SortOrder_K+1.
ByVector_K vectors must all be a column vector or all be a row vector. In case they are column vectors, they must have same count of rows as DataSource. DataSorce is ordered by exchanging rows in that case. In case ByVector_K are row vectors, they must have same count of columns as DataSource. DataSorce is ordered by exchanging columns in that case.
SORTBY reports an error in parameter list(Err:504) if the conditions are not met.
Additional details:
- SORTBY uses the default sorting algorithm alpha-numeric of the locale specified in ▸ ▸ ▸ . That is the same algorithm as used by ▸ and ▸ .
- SORTBY is case insensitive. The setting Case sensitive in section General Calculations in ▸ ▸ ▸ is ignored.
- SORTBY has no means to specify whether the data source has a row or a column with labels.
- The result of the SORTBY function does not get formatting from the DataSource but uses the style of the target range.
- SORTBY can use inline arrays and function results for parameter ByVector_K.
- SORTBY is an array function, details see below.
General information about Calc's array functions
To avoid repetition within the subsequent text, only Windows/Linux key combinations are specified. The following table shows the mapping between these key combinations and their macOS equivalents.
| Windows/Linux key combination | macOS equivalent | Comments |
|---|---|---|
| Ctrl + ⇧ Shift + ↵ Enter | ⌘ Cmd + ⇧ Shift + ↵ Enter | Create an array formula. |
| Ctrl + / | ⌘ Cmd + / | Select all cells in an array formula range. "/" is the division key on the numeric keypad. |
| Ctrl + C | ⌘ Cmd + C | Copy. |
| Ctrl + V | ⌘ Cmd + V | Paste. |
- An array is a rectangular range of cells containing data. For example, a square range of three rows by three columns is a 3 x 3 array. The smallest possible array is a 1 x 2 or 2 x 1 array, with two adjacent cells.
- A formula in which the individual values in a cell range are evaluated is referred to as an array formula. The difference between an array formula and other formulas is that the array formula deals with several values simultaneously instead of just one.
- If you create an array formula using the Function Wizard, you must mark the Array check box so that the results are returned in an array. Otherwise, only the value in the upper-left cell of the array being calculated is returned.
- If you enter the array formula directly into the cell, you must use the key combination Ctrl + ⇧ Shift + ↵ Enter instead of just the ↵ Enter key. Only then does the formula become an array formula.
- An array formula is displayed inside curly brackets or braces ("{" and "}"). It is not possible to create an array formula by manually entering these braces.
- Some array functions take parameters that are forced to evaluate as an array formula, even when the formula is entered normally. These are MDETERM, MINVERSE, MMULT, SUMPRODUCT, SUMX2MY2, SUMX2PY2, and SUMXMY2.
- Calc supports constant inline arrays in formulas. An inline array is enclosed within curly brackets or braces ("{" and "}"). Individual elements can be numbers (including negatives), logical constants (TRUE, FALSE), or literal strings. Non-constant expressions are not allowed. Arrays can be entered with one or more rows, and one or more columns. All rows must comprise the same number of elements; similarly, all columns must comprise the same number of elements.
- Editing array formulas. Select the cell range containing the array formula. To select the whole range, position the cursor inside the range and press Ctrl + /. Then press F2 or position the cursor in the Input line, edit the formula as required, and use the key combination Ctrl + ⇧ Shift + ↵ Enter.
- Deleting array formulas. Select the cell range containing the array formula. To select the whole range, position the cursor inside the range and press Ctrl + /. Press Delete to delete the array contents, including the array formula. Alternatively, press ← Backspace to bring up the Delete Contents dialog box, select Formulas or Delete all, and click .
- Copying array formulas. Select the cell range containing the array formula. To select the whole range, position the cursor inside the range and press Ctrl + /. Then press F2 or position the cursor in the Input line. Copy the formula by pressing Ctrl + C. Select a range of cells where you want to insert the array formula and either press F2 or position the cursor in the Input line. Paste the formula by pressing Ctrl + V in the selected space and confirm it using the key combination Ctrl + ⇧ Shift + ↵ Enter. The selected range now contains the array formula.
- Adjusting an array range. Select the cell range containing the array formula. To select the whole range, position the cursor inside the range and press Ctrl + /. Below the selection, to the right, you will see a small icon with which you can zoom in or out on the range using your mouse. When you adjust the array range, the array formula will not be adjusted automatically - you are only changing the range in which the results will appear.
Examples:
The following table is used in examples below. The table shows an excerpt from a list of children in a grade at a (fictitious) school.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Class | Name | Age | Gender |
| 2 | 5A | Mia | 10 | female |
| 3 | 5B | Evelyn | 10 | female |
| 4 | 5B | Noah | 10 | male |
| 5 | 5A | Alice | 11 | female |
| 6 | 5A | Arthur | 12 | male |
| 7 | 5A | Isabella | 9 | female |
| 8 | 5B | Terry | 9 | male |
| 9 | 5A | George | 11 | male |
| 10 | 5B | Mary | 10 | female |
| 11 | 5B | Betty | 11 | female |
| 12 | 5A | Luca | 10 | male |
| 13 | 5B | Oscar | 11 | male |
| 14 | 5B | Jessica | 12 | female |
| 15 | 5B | Henry | 10 | male |
| 16 | 5A | Thomas | 11 | male |
Sort one column
You need not use an entire block of cells with content as DataSource. The formula {=SORTBY(B2:B16;B2:B16)} in cell range F2:F16 results in an alphabetic list of the names.
| … | F | … | |
|---|---|---|---|
| 1 | |||
| 2 | Alice | ||
| 3 | Arthur | ||
| 4 | Betty | ||
| 5 | Evelyn | ||
| 6 | George | ||
| 7 | Henry | ||
| 8 | Isabella | ||
| 9 | Jessica | ||
| 10 | Luca | ||
| 11 | Mary | ||
| 12 | Mia | ||
| 13 | Noah | ||
| 14 | Oscar | ||
| 15 | Terry | ||
| 16 | Thomas |
Since the SORTBY functions does not detect labels, the formula ={SORTBY(B1:B16;B1:B16)} in cell range F1:F16 results in a list where the label Name is moved between entry Mia and Noah.
Multi-level sort
The formula {=SORTBY(A2:D16;A2:A16;;C2:C16;-1)} results in a two-level sort. First the list is sorted by Class in ascending order and then the rows are sorted descending by Age inside each class.
| … | F | G | H | I | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | 5A | Arthur | 12 | male | |
| 3 | 5A | Alice | 11 | female | |
| 4 | 5A | George | 11 | male | |
| 5 | 5A | Thomas | 11 | male | |
| 6 | 5A | Mia | 10 | female | |
| 7 | 5A | Luca | 10 | male | |
| 8 | 5A | Isabella | 9 | female | |
| 9 | 5B | Jessica | 12 | female | |
| 10 | 5B | Betty | 11 | female | |
| 11 | 5B | Oscar | 11 | male | |
| 12 | 5B | Evelyn | 10 | female | |
| 13 | 5B | Noah | 10 | male | |
| 14 | 5B | Mary | 10 | female | |
| 15 | 5B | Henry | 10 | male | |
| 16 | 5B | Terry | 9 | male |
Horizontal sort
The orientation of byVector_K determines whether rows are exchanged or columns. This example uses an inline row-vector to exchange columns. The formula ={SORTBY(A1:E16;{4;1;3;2})} in cell range F1:I16 results in a table with column order Name, Gender, Age, Class. It shows, that the byVector_K need not be part of the DataSource.
| … | F | G | H | I | |
|---|---|---|---|---|---|
| 1 | Name | Gender | Age | Class | |
| 2 | Mia | female | 10 | 5A | |
| 3 | Evelyn | female | 10 | 5B | |
| 4 | Noah | male | 10 | 5B | |
| 5 | Alice | female | 11 | 5A | |
| 6 | Arthur | male | 12 | 5A | |
| 7 | Isabella | female | 9 | 5A | |
| 8 | ⁞ | ⁞ | ⁞ | ⁞ |
Shuffle
The parameters of the SORTBY function may be results of formulas. Take as example the following table
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Alice | Arthur | Betty | Evelyn | George | Henry | Isabella | Jessica | Luca | Mary | Mia |
The formuala {={SORTBY(A1:K1;RANDARRAY(1;11))} will result in a row with a randomized order of the eleven names in cell range A1:K1. Since the RANDARRAY function is volatile, each recalculation of the spreadsheet will result in a new order.
Find more about the RANDARRAY function in the help [1].
Structured references
The SORTBY function can be used with structured references. If the example table in cell range A1:D16 was named as database range myData, then you can use the formula {={SORTBY(myData[#Data];myData[[#Data];[Name]])} to get a copy of the table sorted by the column Name. The restriction of the input to [#Data] is necessary because the SORTBY function does not detect labels. Find more about structured references on Wiki page CalcTableRef.
Related LibreOffice functions:
ToDo: [[Special:MyLanguage/Documentation/Calc_Functions/SORT|{{Calc_fn_name|SORT}}]]
ToDo: wiki.documentfoundation.org/Documentation/Calc_Functions/SORT
For now see SORT function in the help [2].
ODF standard:
SORTBY is not yet standardized. Adding this function to the ODF standard is tracked in issue OFFICE-4158.
See instead
COM.MICROSOFT.SORTBY
SORTBY is available in LibreOffice since version 24.8.
Related (or similar) Excel functions:
SORTBY