Convert specific columns into rows in Google Sheets

1
votes

I'm manipulating data in Google Sheets but can't manage to resolve this issue.

In Google Sheets, I have the following table:

name1 | dataA | dataB | dataC
name2 | dataD | dataE | dataF

I would like to convert it into:

name1 | dataA
name1 | dataB
name1 | dataC
name2 | dataD
name2 | dataE
name2 | dataF

Does someone know how to do that in Google Sheets?

2
google-sheetstransposegoogle-sheets-formulaarray-formulasgoogle-sheets-query

2 Answers

0
votes

Assuming your data is in ColumnsA:D and that , is your separator, try:

=query({query(A:D,"select A,B");query(A:D,"select A,C");query(A:D,"select A,D")},"where Col1 is not NULL order by Col1")
0
votes
=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(LEN(A1:A), "♠"&A1:A&"♦"&B1:D, )), , 999^99)), , 999^99), "♠")), "♦")))

0