Convert 2D range to 1D range in Google Sheets

user_11142570 0
Viewed 844

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.

Preferred Final Output

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

google-sheets
3 Answers

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

=FLATTEN(TRANSPOSE(1:52))

enter image description here

user_121888610

use this:

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

enter image description here

user_56326290

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();
}

enter image description here

user_112252910
Related