CODEWITHSUNDEEP
google-sheets
mrjoechu
mrjoechu

Reputation: 35

Q: Transpose -> Merge(?) on google sheets

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.

Upvotes: 1

Views: 1544

Answers (3)

mdcq
mdcq

Reputation: 2026

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)<>"")

Upvotes: 0

ttarchala
ttarchala

Reputation: 4567

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")

Upvotes: 1

Max Makhrov
Max Makhrov

Reputation: 18707

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

Upvotes: 1

Related Questions