Transforming Rows or Columns with the WRAPCOLS Function in Google Sheets

GiGiKing·March 17, 2024·0 comments

Are you tired of using complex formulas like OFFSET, QUERY, or VLOOKUP and SEQUENCE to transform rows or columns into multiple columns in Google Sheets? Well, I have an exciting solution for you! Let me introduce you to the WRAPCOLS function.

What is the WRAPCOLS Function?

The WRAPCOLS function is a powerful array function in Google Sheets that allows you to easily transform a row or column into multiple columns. It’s a handy alternative to using multiple formulas and provides a more efficient way to organize your data.

How does it work?

The WRAPCOLS function takes the following arguments:

  • Range: The range or array you want to wrap. It can be a single column or row.
  • Wrap_count: The maximum number of cells for each column in the output. This determines how many columns the data will be wrapped into.
  • Pad_with (optional): This argument allows you to replace #N/A in blank cells with a given value.

If the wrap_count is not a whole number, the function will round it down to the nearest whole number. For example, if you have ten cells in the range and the wrap_count is 6, the function will return #N/A in the eleventh and twelfth cells.

How to Use the WRAPCOLS Function

Let’s dive into an example to see the WRAPCOLS function in action. Imagine you have Q1, Q2, Q3, and Q4 month-wise sales values entered in a column under corresponding labels.

WRAPCOLS examples in Google Sheets

To transform this data into multiple columns, you can use the following formula:

=WRAPCOLS(B3:B18, 4)

In the formula above, B3:B18 represents the range, and 4 is the wrap_count. By applying this formula, you will get each quarter’s data in different columns.

The WRAPCOLS Function and Sorting a Two-Dimensional Array

But wait, there’s more! The WRAPCOLS function can also be used to sort a two-dimensional array. Let me show you how.

Let’s consider a scenario where you have sales values under Q1, Q2, Q3, and Q4 columns. You want to sort the monthly sales under each column using a single piece of code. Sounds intriguing, right?

To achieve this, follow these steps:

  1. Copy the field labels from D3:G3 and paste them into D10:G10.

  2. Create a 3 x 4 matrix using the MAKEARRAY formula:

    =MAKEARRAY(3,4,LAMBDA(r,c,c+0))

  3. Flatten the matrix using the FLATTEN formula:

    =FLATTEN(MAKEARRAY(3,4,LAMBDA(r,c,c+0)))

  4. Flatten the values (excluding the field labels):

    =FLATTEN(D4:G6)

  5. Combine the two formulas to create a two-column array:

    ={FLATTEN(MAKEARRAY(3,4,LAMBDA(r,c,c+0))), FLATTEN(D4:G6)}

  6. Sort the array based on column 1 (field labels) in ascending order and column 2 (sales figures) also in ascending order:

    =SORT({FLATTEN(MAKEARRAY(3,4,LAMBDA(r,c,c+0))), FLATTEN(D4:G6)}, 1, 1, 2, 1)

  7. Extract the second column using the INDEX function:

    =INDEX(SORT({FLATTEN(MAKEARRAY(3,4,LAMBDA(r,c,c+0))), FLATTEN(D4:G6)}, 1, 1, 2, 1), 0, 2)

  8. Finally, use the WRAPCOLS function to wrap the one-dimensional array into a two-dimensional array:

    =WRAPCOLS(INDEX(SORT({FLATTEN(MAKEARRAY(3,4,LAMBDA(r,c,c+0))), FLATTEN(D4:G6)}, 1, 1, 2, 1), 0, 2), 3)

That’s it! You have successfully sorted a two-dimensional array using the WRAPCOLS function.

Conclusion

The WRAPCOLS function in Google Sheets is a game-changer when it comes to transforming rows or columns into multiple columns. It simplifies complex tasks and makes data organization a breeze. Say goodbye to long and convoluted formulas!

If you want to learn more about Google Sheets and explore other valuable tips and tricks, visit Crawlan.com. Stay tuned for more exciting content!

GiGiKing

Related posts