How to Partially Flatten a Multi-Column Array in Google Sheets

Published on

You may be stuck at one point when you want to partially flatten a multi-column array in Google Sheets.

As you may know, flattening means making all the values from one or more arrays into a single column.

It’s not similar to TRANSPOSE, which we use to change the orientation of one or more arrays.

I can probably explain the topic well with an example.

I have data in four columns in a Sheet, and its structure is as follows.

The first column contains descriptions of a few construction activities (tasks), and the adjoining three columns their week-wise completion weightage.

I want to partially-flatten this multi-column array using the FLATTEN function in Google Sheets.

I mean, I want to flatten the last three columns (task weightage) and properly align the first column values (tasks) with them.

So there will only be two columns in the final output, and they are activities and weightage.

Usually, we will try this method unsuccessfully.

F2:

={A3:A8}

G2:

=flatten(B3:D8)
Partially Flatten a Multi-Column Array - Example 1

As you can see in the range F2:G19, the partially flattened range/array (B3:D8) is not aligned with the values in the range A3:A8, and of course, it won’t.

You may require to insert two blank cells between each activity in F2:F7 to row-wise match the partially flattened data.

You can find the solutions to do that below.

Partially Flattening a Multi-Column Array into Two Columns

I have used the following two formulas to partially flatten a multi-column array in Google Sheets (please refer to the range I2:J19).

I2:

=ArrayFormula(flatten({A3:A8,iferror(A3:B8/0)}))

J2:

=flatten(B3:D8)

Let’s learn it below.

Note:- You can combine I2 and J2 formulas and use them as a one-piece formula in I2 alone.

=ArrayFormula({flatten({A3:A8,iferror(A3:B8/0)}),flatten(B3:D8)})

But I won’t recommend this as it may cause issues when you want to keep more than one column untouched. We will discuss that later.

Formula Explanation

First, let’s learn the I2 formula, which inserts the blank cells between tasks.

We want to return two blank cells below each activity (tasks). How is it possible?

We can get that by flattening one value column and two blank columns.

Generic Formula: =flatten(1_value_column,2_blank_columns)

1_value_column – A3:A8

2_blank_columns – iferror(A3:B8/0)

In Google Sheets, we can use IFERROR as above to create two virtual blank columns.

We should wrap this flatten with the ArrayFormula as IFERROR is a non-array function.

That’s what we have done!

This way, we can partially flatten a multi-column array in Google Sheets.

Note:-

Assume you have infinite column arrays.

For example, A3:A and A3:B in the I2 and B3:D in the J2 formulas. If so use FILTER with the formulas as below.

I2:

=flatten({filter(A3:A,A3:A<>""),filter(iferror(A3:B/0),A3:A<>"")})

J2:

=flatten(filter(B3:D,A3:A<>""))

Partially Flattening a Multi-Column Array into Three Columns

In the above construction schedule, i.e. in the range A2:D8, I want to insert one more column between A and B to put the total weightage of each activity.

Modified Data:

Partially Flatten a Multi-Column Array - Example 2

Here I want to flatten the last three columns and align the first two columns with them.

To partially flatten a multi-column array into three columns, here also, we can use my earlier two formulas in G3 and I3 after changing the references.

We will flatten C3:E8 straightaway in I3 and insert blank cells in A3:A8 in G3.

G3:

=ArrayFormula(flatten({A3:A8,iferror(A3:B8/0)}))

I3:

=flatten(C3:E8)

That’s not enough! We should insert blank cells between the values (weightage) in B3:B8 and get them in H3:H20.

This will do that.

H3:

=ArrayFormula(flatten({B3:B8,iferror(A3:B8/0)}))

More than Three Columns – Additional Tips

If you have several columns to keep on the left side of the data, I will suggest using a Vlookup Array Formula.

I’ll explain how.

In the above example, you can replace the H3 formula with the following.

=ArrayFormula(ifna(vlookup(G3:G18,A3:B8,{2},0)))

Vlookup will search the tasks in G3:G18 in A3:B8 and will return the total weightage from B3:B8.

How is this formula beneficial?

By changing {2} with {2,3} you can return values from two columns.

I hope the below screenshot is self-explanatory.

4-column output - example

That’s all about how to partially flatten a multi-column array in Google Sheets.

Thanks for the stay. Enjoy!

Related:- A Simple Formula to Unpivot a Dataset in Google Sheets.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.