Consolidate columns from a dynamic range into a single column in Google Sheets

Question

How can Google Sheets take a range of multiple columns and rows and stack or consolidate all the non-empty cells into one column? For this case, I want non-unique cells. Also, each cell in the range is a string that has commas and spaces so if needed, a special char will need to be as a delimiter.

There are several questions already that are about concatenating columns, but they are all concatenating the values of the columns and not the actual columns - something such as this sudo SQL statement such as SELECT * AS single_column or if the range was not dynamic an equivalent SQL could use union such as:

SELECT col_a AS single_colum FROM foo
UNION ALL
SELECT col_b AS single_colum FROM foo
UNION ALL
SELECT col_b  AS single_colum FROM foo
...

Answer 1

If you really need to handle dynamic references having a lot of columns, then consider to use Google Apps Script because while it could be possible to build formula to achieve the desired results it's very likely that the resulting formula will be very convoluted, hard to maintain an error prone. To learn about using Google Apps Script to extend Google Sheets please read https://developers.google.com/apps-script/guides/sheets

Related

• Google Sheet - Transform two columns into one column using arrayformula (more than 50,000 characters) (Stack Overflow)

---

If you are able to handle the references in a case by case basis, try the array handling feature of Google Sheets. The following formula returns one column having the values of Columns A, B and C from Sheet1.

Example. Assuming that your values are on sheet called Sheet1, on another sheet add the following formula:

={Sheet1!A:A;Sheet1!B:B;Sheet1!C:C}

Tip: Remove the bottom blank rows from Sheet1, otherwise you will have a lot of blank rows between the last non blank and first blank values and the sheet having the above formula will have three times the number of rows of Sheet1.

In case that you can't to remove the blank rows you could filter them. Assuming that Column A, B an C have values of the same type you could use QUERY

=QUERY({Sheet1!A:A;Sheet1!B:B;Sheet1!C:C},"select Col1 where Col1 is not null")

Related

• Can you do a UNION SELECT in a Google Sheet?

Reference

• Using arrays in Google Sheets

Answer 2

• to consolidate columns into one single column:

  =TRANSPOSE(QUERY(TRANSPOSE(A1:C),,999^99))

  

• to consolidate rows into one single row:

  =QUERY(A1:C,,999^99)

  

• to create truly dynamic range:

  =INDIRECT("Sheet2!A1:"&ADDRESS(ROWS(Sheet2!A:A), COLUMNS(Sheet2!1:1), 4))

• combined:

  =TRANSPOSE(QUERY(TRANSPOSE(
   INDIRECT("Sheet1!A1:"&ADDRESS(ROWS(Sheet1!A:A), COLUMNS(Sheet1!1:1), 4))),,999^99))