Convert 2D range to 1D range in Google Sheets

Question

How can I dynamically rearrange a 2D range in Google Sheets into a 1D range, where the rows are "stacked" on top of each other, like shown below? When I say dynamically, I mean that the range is of a dynamic size using a FILTER, with a unknown number of columns (with a known number of rows though (52)). I have tried using the SPLIT(JOIN(range)) method seen on other questions, but that does not work here as JOIN requires the range(s) to be 1D.

*Looking for Google Sheets solution if possible, but a Google Script would also be fine

There's an undocumented function called `FLATTEN()`. Give that a shot. — General Grievance, Jan 07 '21 at 21:27
@Calculuswhiz actually its documented: https://support.google.com/docs/answer/10307761?hl=en — player0, Jan 07 '21 at 22:19

score 4 · Accepted Answer · answered Jan 07 '21 at 23:07

4

For unknown number of cols and known rows you can just use:

=FLATTEN(TRANSPOSE(1:52))

answered Jan 07 '21 at 23:07

basic

11,673
2
9
26

player0 · Answer 2 · 2021-01-07T23:44:26.187

2

use this:

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

edited Jan 07 '21 at 23:44

answered Jan 07 '21 at 22:18

player0

124,011
12
67
124

score 0 · Answer 3 · answered Jan 07 '21 at 23:46

Of course the other answers are the most preferable for many reasons.

For documentation purposes and since you are open to a google apps script solution, here is a custom function that you could use:

function myFlat(rng) {
  const array = SpreadsheetApp.getActiveSheet().getRange(rng).getValues();
  return array[0].map((_, colIndex) => array.map(row => row[colIndex])).flat();
}

Convert 2D range to 1D range in Google Sheets

3 Answers3