SortData
SortData() provides sorting capabilities to Quintessence functions.
Excel provides functionality to sort a spreadsheet or range of cells according to a hierarchy of columns, for example: Order by Column A descending, Column C ascending and then Column B ascending. However, this functionality cannot be used in conjunction with functions that output dynamic ranges. This is because Excel includes the header cell in the sort (the cell containing the Quintessence function), resulting in an incorrect result.
The SortData() function takes a range of data as input and sorts the range according to any column or hierarchy of columns. If headings from the input range need to be included in the output, the headings can be skipped from the sort so that they appear in the correct place.
Syntax
SortData (Range, Sort Column Index, Skiplines)
| Range | The range of cells in Excel to be sorted |
| Sort Column Index | A comma separated list of positive and negative integers. Each number represents a column to sort (1 = the first column, 2 = the 2nd column, etc.) A positive integer means order from smallest to largest (ascending). A negative integer means order from largest to smallest. For example, “3,-2” first sorts on column 3 (ascending) and then on column 2 (descending). |
| Skiplines | This indicates how many lines to skip from the sort. If the data has headings, skipping the heading lines ensures that the headings stay at the top of the sorted range. |
Note:
Input parameters can consist of Quintessence functions. This includes functions that return ranges in cases where a parameter value can be a range.
Examples
Example 1. Sorting by number of constituents (ascending) and value (descending)
Example 2. Using SkipLines to prevent column headings from being sorted
Example 3. Forgetting to use SkipLines, resulting in column headings being sorted
Note:
How your system administrator configured Quintessence determines which function parameters are required. In most implementations, administrators configure functions so that certain parameters default to specific values when left blank.
Example 1. Sorting by number of constituents (ascending) and value (descending)
The SortData function is used to order a range of information by the number of Constituents (ascending) and the Value (descending).
| Range | Sort by column 2 ascending, column 3 descending | ||
| Sector | Constituents | Value | |
| Basic Materials | 40 | 6000 | =SortData(A3:C7,”C2,-C3″) |
| Consumer Goods | 21 | 2011 | |
| Telecommunications | 12 | 1200 | |
| Health Care | 40 | 2500 | |
| Industrials | 12 | 7800 | |
| Range | Output | ||||
| Sector | Constituents | Value | |||
| Basic Materials | 40 | 6000 | Industrials | 12 | 7800 |
| Consumer Goods | 21 | 2011 | Telecommunications | 12 | 1200 |
| Telecommunications | 12 | 1200 | Consumer Goods | 21 | 2011 |
| Health Care | 40 | 2500 | Basic Materials | 40 | 6000 |
| Industrials | 12 | 7800 | Health Care | 40 | 2500 |
Example 2. Using SkipLines to prevent column headings from being sorted
The SortData() function is used to order a range of records by value (ascending). The headings are included in the input. Setting SkipLines to 2 means that two rows are omitted by the sort function in order to prevent the headings being sorted in the output.
| Range | Sort by column 2 ascending, column 3 descending | ||
| Value as at 31 March 2015 | =SortData(A2:C8,”C3″,2) | ||
| Sector | Constituents | Value | |
| Basic Materials | 40 | 6000 | |
| Consumer Goods | 21 | 2011 | |
| Telecommunications | 12 | 1200 | |
| Health Care | 40 | 2500 | |
| Industrials | 12 | 7800 | |
| Range | Output | ||||
| Value as at 31 March 2015 | Value as at 31 March 2015 | ||||
| Sector | Constituents | Value | Sector | Constituents | Value |
| Basic Materials | 40 | 6000 | Telecommunications | 12 | 1200 |
| Consumer Goods | 21 | 2011 | Consumer Goods | 21 | 2011 |
| Telecommunications | 12 | 1200 | Health Care | 40 | 2500 |
| Health Care | 40 | 2500 | Basic Materials | 40 | 6000 |
| Industrials | 12 | 7800 | Industrials | 12 | 7800 |
Example 3. Forgetting to use SkipLines, resulting in column headings being sorted
The SortData() function is used to order a range of records by the number of constituents (ascending) and value (descending). The headings are included in the input. No rows are excluded from the sort, resulting in the headings being sorted as part of the output.
| Range | Sort by column 1 ascending | ||
| Value as at 31 March 2015 | =SortData(A2:C8,”C1″) | ||
| Sector | Constituents | Value | |
| Basic Materials | 40 | 6000 | |
| Consumer Goods | 21 | 2011 | |
| Telecommunications | 12 | 1200 | |
| Health Care | 40 | 2500 | |
| Industrials | 12 | 7800 | |
| Range | Output | ||||
| Value as at 31 March 2015 | Basic Materials | 40 | 6000 | ||
| Sector | Constituents | Value | Consumer Goods | 21 | 2011 |
| Basic Materials | 40 | 6000 | Health Care | 40 | 2500 |
| Consumer Goods | 21 | 2011 | Industrials | 12 | 7800 |
| Telecommunications | 12 | 1200 | Sector | Constituents | Value |
| Health Care | 40 | 2500 | Telecommunications | 12 | 1200 |
| Industrials | 12 | 7800 | Value as at 31 March 2015 | ||