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, 2019 at 1:30
  • @user0 Sure, did that help? May 19, 2019 at 1:32

1 Answer 1

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, 2019 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, 2019 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, 2019 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, 2019 at 2:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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