In Google Sheets, how to transpose multiple column headers and data into a single column of multiple rows (I Column to 1 row each)

Question

My question is very close to that other one:

In Google Sheets, how to transpose multiple column headers and data into a single column of multiple row labels and data

My Input (minimal example of 3 columns, but in reality 100's of columns):

GROUP 1	GROUP 2	GROUP 3
aa	b	cc
aaa	bb	cc
aaaaa	bbb	ccc
aa	bbb	ccc
a	bbb	ccc
	bbb
	bbbb

Needed Output:

GROUP 1	aa	aaa	aaaaa	aa	a
GROUP 2	b	bb	bbb	bbb	bbb	bbb
GROUP 3	cc	cc	ccc	ccc	ccc

The columns need to be with dynamic ending because their lengths are irregular.

So far I've only managed to use the usual transpose method by manually transposing each columns as so:

IN COLUMN F1:F:

=transpose(A1:A)
=transpose(B1:B)
=transpose(C1:C)
etc.

That is an error prone and very manual process for hundreds of columns.

What way would be a more efficent process to modify @player0 's formula in linked post to suit the above use? Your help is appreciated very much.

Answer 1

In order to provide a proper response to the question, I'm writing this answer as a community wiki, since the issue was resolved from the comments section.

=TRANSPOSE(A:C)

References

• https://support.google.com/docs/answer/3094262