Transposing varied number of columns per entry into duplicate entries with one column in Google Sheets

I feel almost certain this has been answered, but in a time crunch I am hoping someone can outline the solution for me, as my searches returned variations that were not quite what I wanted.

I have data that is arranged with multiple columns per entry:

I would like to transpose the columns data into rows duplicating the first two columns as indices:

Much appreciation for any solutions.

As you have noted, there is a precedent for this on either StackOverflow or webapps but I couldn't find it, so I've recreated an equivalent solution. I'm pretty sure that this formula can be simplified BUT it does work - so simplification is for another day/user.

Use this formula (assumes that the raw data is in sheet="Data"):

`=query({{"Number","Name","Code"};sort(query({query({query({query({query(Data!A2:B6) ,query(Data!A2:F6) },"select Col1, Col2, Col5 where Col1 = Col3");query({query(Data!A2:B6) ,query(Data!A2:F6) },"select Col1, Col2, Col6 where Col1 = Col3 and Col6 is not null")});query({query(Data!A2:B6) ,query(Data!A2:F6) },"select Col1, Col2, Col7 where Col1 = Col3 and Col7 is not null")});query({query(Data!A2:B6) ,query(Data!A2:F6) },"select Col1, Col2, Col8 where Col1 = Col3 and Col8 is not null")}),2,true)})`

The logic is

• create four(4) x two-array queries: the first part is Columns A & B (Col1 and Col2), the second part is Columns A to F (Col3 to Col8)
• each query lists Col1 and Col2 and one of Col5 to Col 8 respectively.
• these quesries are then stacked. Note that are joined between curly brackets and separated by a semicolon `{;}`
• the end result is then sorted using `SORT`
• the last step is to add the column headings (though this is optional) by creating another two-array query

BEFORE

AFTER

• My actual data set was a bit bulkier, so this did become a bit expansive with all the copy pasting of the queries. I would still be interested if anyone comes up with a more streamlined approach. This did teach me some new techniques. Sep 8, 2020 at 12:24

Another option:

``````=ArrayFormula({"Number","Name","Code";SPLIT(QUERY(FLATTEN(IF(FILTER(C2:F,A2:A<>"")="","",FILTER(A2:A&"\"&B2:B,A2:A<>"")&"\"&FILTER(C2:F,A2:A<>""))),"Select * Where Col1 Is Not Null"),"\")})
``````

The first part of the inner virtual array (before the semicolon) creates the headers.

You'll notice that all ranges in the formula have FILTER applied. This is so that we can only deal with rows that have numbers assigned in Column A.

Jumping to the IF clause, if anything in the C:F range is null, we want it left null (i.e., we don't want any concatenation to happen). If not, we want to concatenate the Column A data, a backslash, the Column B data, another backslash, and every the data of every separate non-null cell in the C2:F range. So the first entry, given your sample data, would look like this in virtual space: 123\abc\9081

FLATTEN will take all of these strings and form one column from them (which will include all the nulls at this point). NOTE: FLATTEN is as yet an undocumented Google Sheets function.

QUERY condenses the FLATTENed list, squashing out the nulls.

Finally, SPLIT simply splits all the entries at the backslash, forming the three columns.

This is what I came up with on the spot here. As you say, there may be other methods elsewhere online.

• This worked really well, and I learned some new techniques. I accepted the other solution because I used it first. Thank you! Sep 8, 2020 at 12:22
• Glad you got practical use and knowledge out of it, Zachary. The most important things are that A.) you get the help you came for and B.) you mark something as "Best Answer" if it works, because this signals to other contributors here than an issue is resolved without our having to leave the master listing. Sep 8, 2020 at 15:49