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)

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:

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.

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.