Create a repeating list based off of a range and a specific numbers of repetitions.
Contents:
Formula
To creating a dynamic repeating sequence use this formula:
= TOCOL(CHOOSECOLS(input_range, SEQUENCE(1,repetitions,1,0)))input_range - the range of cells, values, or items to repeat
repetitions - the number of times each item will be repeated
Explanation
This formula can be broken down into a few different functions and steps.
The three main functions used are:
TOCOL - Turns an array into a single column
CHOOSECOLS - Returns specific columns from a specified array
SEQUENCE - Creates a sequence of values
= TOCOL(CHOOSECOLS(input_range, SEQUENCE(1,repetitions,1,0)))In this formula, the input_range is the range of cells that you want to repeat, while repetitions is the number of times each item will be repeated.
The SEQUENCE() function creates a sequence of numbers, and is used to direct the CHOOSECOLS function to repeat our input range. The 1 in the first argument is the number of rows to create, the column argument is the number of repetitions, and the following 1 and 0 values are start value (1) and the increment value (0), so our sequence starts at 1 and repeats 1 depending on the reptation number.
Then the CHOOSECOLS function replaces each "1" from the SEQUENCE function, with our input range. Ending up with something like this:
The final step is to use the TOCOL function, which takes a two-dimensional array like the one above, and outputs the array into a single vertical column like we see on the left here.
This creates a spilled, dynamic array that will automatically adjust its size to match both our input values, and the
number of times each is repeated.
In this example, we have a list of 6 fruit, and are are repeating them 3 times each to form our final output array.