ZIAD
  • Home
  • Posts
  • About

WRAPCOLS in Google Sheets

2022-09-16 • 2 min read

In this article I will explain how to implement the Excel WRAPCOLS() function in Google Sheets.

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")
WRAPCOLS Example

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.
To import this function in your spreadsheet go to Data > Named functions > Import function select the Named Functions spreadsheet and import _WRAPCOLS.

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))
First step

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))
Second step

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"))
Third step

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


Note

The Named Function has a FLATTEN() wrapper on every occurrence of the input array. This is done to ensure that the formula works even if the array is not vertical.

That's all. Thanks for reading.

Previous Post Next Post

Published on September 16, 2022

Table of Contents

  • Named Function
  • Explanation
    • Step 1 - Assign identifier
    • Step 2 - Create lookup array
    • Step 3 - Run a VLOOKUP
PE Profile - Stack Overflow - Email - LinkedIn - X
© 2023 Ziad.net