Jump to content

Documentation/Calc Functions/SORTBY

From The Document Foundation Wiki
This page contains changes which are not marked for translation.


Under construction.
For now, see online help for SORTBY.

Especially the names of the parameters are not final.


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_1SortOrder_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_2ByVector_N (optional) specify additional levels of sorting. Data records with same value in ByVector_1ByVector_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 Tools ▸ Options ▸ Languages and Locales ▸ General. That is the same algorithm as used by Data ▸ Sort Ascending and Data ▸ Sort Descending.
  • SORTBY is case insensitive. The setting Case sensitive in section General Calculations in Tools ▸ Options ▸ LibreOffice Calc ▸ Calculate 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

Note:
For convenience, the information in this subsection is repeated on all pages describing 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.

Mapping between relevant Windows/Linux and macOS key combinations
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 OK.
  • 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