Skip to content

Instantly share code, notes, and snippets.

@jack-williams
Last active June 15, 2023 09:35
Show Gist options
  • Save jack-williams/75f6ea2e9745cc31f1733450977db86c to your computer and use it in GitHub Desktop.
Save jack-williams/75f6ea2e9745cc31f1733450977db86c to your computer and use it in GitHub Desktop.
Excel list functions
/**
* Tests whether the input is a list.
* A list is an array with one row or column.
*/
isList = LAMBDA(input, AND(TYPE(input) = 64, OR(ROWS(input) = 1, COLUMNS(input) = 1)));
/**
* Applies an array-returning function to each element of the input list,
* returning a flattened array. The result are arranged vertically.
* ```
* flatMap({1; 2; 3}, LAMBDA(x, SEQUENCE(x))) ==> { 1; 1; 2; 1; 2; 3}
* ```
*/
flatMap = LAMBDA(input, function,
IF(
NOT(isList(input)),
#VALUE!,
DROP(REDUCE("", input, LAMBDA(prev, current, VSTACK(prev, function(current)))), 1)
)
);
/**
* Returns a list that is `count` repetitions of the original list.
* ```
* repeat({1; 2}, 3) ==> { 1; 2; 1; 2; 1; 2}
* ```
*/
repeat = LAMBDA(input, count, flatMap(SEQUENCE(count), LAMBDA(_, input)));
/**
* Tests whether the input list contains the element.
* Comparision function `equals` can be provided to control equality testing.
* If `equals` is omitted then exact matching is used.
*/
contains = LAMBDA(input, element, [equals],
IF(
ISOMITTED(equals),
IF(TYPE(input) = 16, input, NOT(ISERROR(XMATCH(element, input)))),
TYPE(FILTER(input, MAP(input, LAMBDA(x, equals(x, element))), FALSE)) = 64
)
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment