Transpose a table row by row

Question

I need to transpose parts of a table row by row. The following example illustrates what the result needs to look like:

I tried different combinations of arrayformula(), flatten() and transpose(), succeeded with getting the last column right with =arrayformula(FLATTEN(B4:C)), but need now help with fixing this.

Link to table

Answer 1

Try this in row 2 of the example screenshot:

={flatten({A2:A,A2:A}),arrayformula(flatten({if(A2:A<>"",B$1,),if(A2:A<>"",C$1,)})),flatten({B2:C})}

Or if you want the column headings, put this in row 1:

={"A","B","C";flatten({A2:A,A2:A}),arrayformula(flatten({if(A2:A<>"",B$1,),if(A2:A<>"",C$1,)})),flatten({B2:C})}

The new column 'C' ends up being a mixed data type, so be careful if you run a future query on these results as it doesn't like mixed data.

For local implementation (as per your initial screengrab, EU locale file), try this in cell E4 - since the formula sits in row 4, the array range needs to go from 4 (A4:A):

={flatten({A4:A\A4:A})\arrayformula(flatten({if(A4:A<>"";B$3;)\if(A4:A<>"";C$3;)}))\flatten({B4:C})}

Alternatively, if you want column headings, try this in cell E3 - the array range is still A4:A because "A"\"B"\"C"; puts headings in row 3, ; is a return, then the rest of the formula targets data from row 4 down:

={"A"\"B"\"C";flatten({A4:A\A4:A})\arrayformula(flatten({if(A4:A<>"";B$3;)\if(A4:A<>"";C$3;)}))\flatten({B4:C})}

If you want to limit the array range to a specific row rather than working down the entire sheet (eg. row 20), then A4:A would need to be A4:A20.

Answer 2

This is a basic SPLIT(FLATTEN( problem.

Arrayformula() can always exist on the outside of a formula and will apply to the whole thing. This is on a new tab in your sample called MK.Help:

=ARRAYFORMULA(QUERY(TO_TEXT(SPLIT(FLATTEN(Data!A2:A&"|"&Data!B1:C1&"|"&Data!B2:C);"|";0;0));"where Col1<>''"))