How to stack multiple columns into one, alternating rows – Google Sheets

How to stack multiple columns into one, alternating rows – Google Sheets

Problem Description:

I’m working on a Google Sheets document that has data stacked in columns in an unusual order (see image for reference)

I have what you see on columns A, B and C. But I want what I have on column E.
I have 8 pages with hundreds of data elements, all sorted in 3 columns like that and I’m looking for a formula or a custom script that would allow me to arrange the data in the right sequence.

I did research trying to find a solution, but none of the ones I found work well for this situation. The order of the elements is very important.

enter image description here


EDIT:
The data is not numbers. It’s text. So sorting formulas wouldn’t work, because it would sort it alphabetically and it would mess up the order

Solution – 1

try:

=SORT(ARRAYFORMULA(FLATTEN(A1:A,B1:B,C1:C)),1,1)

enter image description here

Solution – 2

all you need is:

=FLATTEN(A1:C7)

with open ranges you can do:

=QUERY(FLATTEN(A1:C); "where Col1 is not null"; )

see: https://stackoverflow.com/questions/73767719/

enter image description here

Rate this post
We use cookies in order to give you the best possible experience on our website. By continuing to use this site, you agree to our use of cookies.
Accept
Reject