1

I'm trying to rotate values in a sheet and then duplicate the first value in the row as a header. For example:

a | b | c | d

would become:

a | b
-----
a | c
-----
a | d

Is there any way to do this with just Google Sheets commands, or do I need to use a script?

2
  • can you provide larger sample of example?
    – user0
    May 19 '19 at 1:30
  • @user0 Sure, did that help? May 19 '19 at 1:32
1

=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(
 TRANSPOSE("♥"&A1&"♦"&B1:D1), , 999^99), "♥")), "♦")))

enter image description here

4
  • 1
    Wow! I'm still trying to wrap my head around how this works, but using ♥ and ♦ as delimiters was a neat trick! Very fast solution as well! Would you mind explaining how the blank query works? May 19 '19 at 1:45
  • 1
    blank query is an alternative to JOIN or TEXTJOIN which are limited to join only 50000 characters. this "query smush" is pretty much limitless (can process 999^99 (= 9.05698E+296) rows of data)
    – user0
    May 19 '19 at 1:51
  • 1
    After picking this apart, I came up with =ARRAYFORMULA(SPLIT(TRANSPOSE("♥"&A1&"♦"&B1:D1), "♦♥", TRUE)), which seems more efficient. Are there any issues with this solution? May 19 '19 at 1:56
  • 1
    nah, that would work too for one row. however, it's not scalable so: i.stack.imgur.com/YumzC.png
    – user0
    May 19 '19 at 2:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.