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?

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

 TRANSPOSE("♥"&A1&"♦"&B1:D1), , 999^99), "♥")), "♦")))

enter image description here

  • 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? – Ethan Chapman May 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 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? – Ethan Chapman May 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 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.