1

Trying to transpose data such that rows transpose into a single column stacking on top of each other.

=ARRAYFORMULA({TRANSPOSE(A1:C1);TRANSPOSE(A2:C2);TRANSPOSE(A3:C3)})

This formula essentially does what I want but what if I have many more rows? Would I need to enter; TRANSPOSE(Col(x):Col(y)) for every single row?

Any help is appreciated.

K.Dᴀᴠɪs
  • 9,945
  • 11
  • 33
  • 43
mrjoechu
  • 35
  • 5

3 Answers3

1

Please try:

=TRANSPOSE(SPLIT(TEXTJOIN(",",1,A:C),","))

enter image description here

Notes:

  • textjoin will join text and skip blanks. Add spaces in column C to have an empty row.
  • limit of join function is 50000 characters
Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
  • Thanks! This is exactly what I needed. Is there a way to get around the 50000 character limit? – mrjoechu Feb 13 '18 at 07:23
  • Your welcome! The limit is known issue, please see answers here: https://stackoverflow.com/questions/22368544/new-google-spreadsheet-concatenate-limit-50000-characters – Max Makhrov Feb 13 '18 at 08:53
1

Max Makhrov's answer is good, but indeed subject to the 50k limit. To get around that, I have recently found another method which is explained in my interlacing answer to another question

In your case this would look something like this (up to arbitrary 9 rows):

=query(
   sort(
     {arrayformula({row(A1:A9)*3, A1:A9});
      arrayformula({row(B1:B9)*3+1, B1:B9});
      arrayformula({row(C1:C9)*3+2, C1:C9})}
   ),
   "select Col2")
Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
ttarchala
  • 4,277
  • 2
  • 26
  • 36
0

Am I missing something, or why does nobody suggest Flatten?

FLATTEN(A1:C3)

And you can use Filter as usual to filter out blank cells, e.g.

=FILTER(FLATTEN(A1:C3);FLATTEN(A1:C3)<>"")
mdcq
  • 1,593
  • 13
  • 30
  • Question was asked in 2018. There was no flatten function then. It's only recently announced like late 2020. – TheMaster Jan 15 '21 at 13:11