google-sheetstransposeflat

Transpose a table row by row

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

enter image description here

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

Solution

  • 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})}

    enter image description here

    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.