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

Question

Not sure what is the proper spreadsheet terminology to word this.

How to turn this:

+-----------+-----------+-----------+...
|Username   |First Name |Second Name|...
+-----------+-----------+-----------+...
|Uname1     |FName1     |SName1     |...
+-----------+-----------+-----------+...
|Uname2     |FName2     |SName2     |...
+-----------+-----------+-----------+...
|Uname3     |FName3     |SName3     |...
+-----------+-----------+-----------+...
:           :           :           :

Into this?:

+-----------+-----------+
|Username   |Uname1     |
+-----------+-----------+
|First Name |FName1     |
+-----------+-----------+
|Second Name|SName1     |
+-----------+-----------+
|Username   |Uname2     |
+-----------+-----------+
|First Name |FName2     |
+-----------+-----------+
|Second Name|SName2     |
+-----------+-----------+
:           :           :

The ellipsis means the multiple columns/rows continues.

I tried googling transposing columns to rows, but couldn't find the exact solution to the question.

Select the whole range; Copy; Move to a new spot on the spreadsheet; Paste Special> Paste as transposed. — Tedinoz, Nov 06 '19 at 04:06
@Tedinoz - no, that doesn't work. Need to transpose multiple column of headers + data, all to two columns of label and data. — S7bvwqX, Nov 06 '19 at 04:17
Apologies; I missed the subtlety of retaining two columns. And @player0 has come to the rescue with an ingenious formula. — Tedinoz, Nov 06 '19 at 08:09

score 4 · Accepted Answer · answered Nov 06 '19 at 06:50

4

try:

=ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(A2:C<>"", "♦"&A1:C1&"♥"&A2:C, )),,999^99)),,999^99), "♦")), "♥"))

answered Nov 06 '19 at 06:50

player0

124,011
12
67
124

Is there a way to add a new line before each new group? For instance in this example a new line before each "Username" – Michael King Mar 31 '21 at 23:35
1

Amazing. Thank you so much. – Michael King Apr 01 '21 at 14:14

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

1 Answers1

Linked