Manipulating Lists

In certain cases, such as when retrieving values from multiple items at once, you may get a list of values separated by a vertical line (also called Pipe, our standard list separator). Here are several functions designed to extract data from these lists.

Creating a list

Sometimes you get a list by retrieving values from sub-items, same group items, board items (See Interacting with related items (ie. other items than the one that triggers the formula)), sometimes you may want to create a list so that you can manipulate it later. You can create a list by simply enclosing the elements in-between quotes and separating them with | (CTRL+P in the editor).

In cases where your elements are separated by characters other than |, or are contained within variables, use the CREATELIST function.:

To create a list of consecutive integers, use RANGE:

Counting the number of elements in a list

To get the number of elements in a list, use LISTCOUNT.

Finding the position of the first element matching a value in the list

To know the position of the first element matching a value, you can use 2 functions: FINDPOSITION or CONTAINSPOSITION.

FINDPOSITION will look for the first element exactly matching the specified text and return its position in the list (0 if not found).

You can use an optional 3rd parameter to specify if the search should be case-insensitive. By default, the search is case-sensitive.

CONTAINSPOSITION works the same way except that it searches for the first element that contains the specified text.

Finding the positions of all elements matching a value

To know the positions of all the elements matching a value, you can use: FINDPOSITIONS or CONTAINSPOSITIONS. Both functions work the same way as their sister functions without the "s" at the end, except they return all positions.

Extracting the element at position X

To get the element at a certain position, use GETELEMENT. If the position is 0 or higher than the number of items in the list, it returns an empty string.

To retrieve the last element, use GETLASTELEMENT.

To retrieve multiple elements, use GETELEMENTS. Same as with its sister function: if the position is 0 or higher than the number of items in the list, it returns an empty string.

Merging 2 lists together

If you want to append a list to another list, use the CREATELIST function seen above.

If you want to merge 2 lists, use the MERGE or FILLANDMERGE functions. With MERGE, each element of the 1st line will be concatenated to the corresponding element of the 2nd list (if any). You can specify a separator as 3rd parameter. This is the function to use if your lists are of the same size.

FILLANDMERGE is useful when the lists are of different sizes. The function will duplicate the last element of the shorted list until both lists match in size.

Filtering a list

To filter a list, use the FILTER function. For instance, to find out how many sub-items statuses are set to Done, use:

To get the positions instead of the values, use POSITIONFILTER.

The 1st parameter of the function is a condition. Each element represented by _each_ needs to meet the condition to be kept in the filtered list. Only operators are accepted in the syntax of the condition, i.e. =,>,<,>=,<=,<>, LIKE, UNLIKE, CSLIKE and CSUNLINE. In other words, you cannot use a function.

For more on filtering lists, see Filtering Lists with the FILTER function and the LIKE operator

Sorting a list

To sort a list, use the SORT function. To sort in descending order, set the optional 2nd parameter to false.

Reversing a list

To reverse a list, use the REVERSE function.

Removing elements

To remove an element from a list, use the REMOVEELEMENT function. By default, if an element appears several times, only the first occurrence is removed. To remove all occurrences, set the optional 3rd parameter to true.

To remove duplicate elements from a list, use the REMOVEDUPLICATES function.

Ranking elements of a list

To get a list of the ranks of the elements of a list, use the RANK function.

Performing a lookup

You may be familiar with the VLOOKUP and HLOOKUP functions available in Excel, Google Sheets, etc. The LOOKUP function is similar. It allows you to look for a value in a list and return the corresponding element in another list.

For instance, the following code will return the value of the HoursSpent column for the sub-item which name is 2023. If you have more than 1 sub-item with this name, it will return only the first one.

If you want to return all the values and not only the 1st one, set the optional 4th parameter to True. In the example below, it will return all the values of the HourSpent column for the sub-items which status is set to Done.

In this case scenario, the LOOKUP function acts as a filter. It creates a new list of values based on a Status filter.

Performing arithmetics on a list

Many standard functions are designed to natively handle lists even though not limited to handling lists, like SUM, MINUS, MULTIPLY, DIVIDE, MAX, MIN, INVERT, etc.

Using the special list arithmetic operators

LISTSUM, LISTMINUS, LISTMULTIPLY and LISTDIVIDE are functions designed specially for lists. These functions perform element-wise operations on two or more lists, calculating results for each corresponding pair of elements from the lists, rather than aggregating all elements together.

See the examples below to understand the difference between SUM and LISTSUM.

These 4 functions return another list. The resulting list contains as many elements as the longest list.

If the lists are not of the same size, the shortest lists are added the missing number of items with a value of 0, except if the list contains only 1 item. In this case, this item is duplicated.

In other words:

is equivalent to:

The exception to this rule is if the 2nd list contains only 1 element in which case:

is equivalent to

Creating a cumulative sum

To create a cumulative sum, use CUMULATIVESUM:

If you don't want to include the current element, i.e., start as 0, set the optional 2nd parameter to true.