Named Function

Description

Wraps the provided array of values by columns after a specified number of elements to form a new array. The provided array is first converted to a vector.

Syntax

_WRAPCOLS(vector, wrap_count, pad_with)

Example

_WRAPCOLS(A1:A10, 3, "x")

Formula Definition

=ARRAYFORMULA(IFNA(VLOOKUP(SEQUENCE(wrap_count,CEILING(ROWS(FLATTEN(vector))/wrap_count)),{SEQUENCE(ROWS(FLATTEN(vector))),FLATTEN(vector)},2,0),pad_with))

• vector - The array to wrap.
• wrap_count - The maximum number of values for each column.
• pad_with - The value with which to pad.

Explanation

Step 1 - Assign a numerical value to each element of the array

The first step is to assign a unique value to each element of the array. This is done using the SEQUENCE() function.

=SEQUENCE(ROWS(A1:A11))

Step 2 - Create a wrap_count × n array

The second step is to create a two-dimensional array of sequential numbers that has wrap_count rows and array_size/wrap_count (rounded to the next integer) columns. This is also done using the SEQUENCE function.

For this example I will assume wrap_count to be 3.

=SEQUENCE(3,CEILING(ROWS(A1:A11)/3))

Step 3 - Run a VLOOKUP()

The final step is to run a vertical lookup with the array from the previous step as the search_key, the array from Step 1 as the search column and the input array as the return column.

=ARRAYFORMULA(IFNA(VLOOKUP(SEQUENCE(3,CEILING(ROWS(A1:A11)/3)),{SEQUENCE(ROWS(A1:A11)),A1:A11},2,0),"x"))

The VLOOKUP is wrapped in an IFNA()which acts as the pad_with parameter of the Named Function.

That's all. Thanks for reading.