# Stacking A Range of Columns Into One (W/O 50,000 Limit)

The Problem:
I have somewhere between 20 and 30 columns of varying lengths (dynamic and can change). I would love the ability to be able to stack them in Google Sheets.

I have found some solutions that work until you reach the `textjoin()` or `concatenate()` result limit of 50,000 or whatever it is. Those formulas are as follows:

`=TRANSPOSE(SPLIT(TEXTJOIN("@",TRUE,TRANSPOSE(G:AB),TRANSPOSE(D1:D5)),"@",FALSE,FALSE))`

&

`=sort(unique(transpose(split(arrayformula(concatenate(if(len(G2:AB)>0,G2:AB&";",""))),";"))))`

Again, these both work but are limited and I expect results as long as 200,000 rows long.

A simplified example is: It would be nice if it rendered only unique values and ignored blank cells. Does not need to be sorted.

BONUS: It would be also very cool if there was a version of this that would only produce cells that have 25 or fewer characters in them!

• Only include microsoft-excel on questions about google-sheets when the question is about something that involve both apps like converting a file from one format to the other.
– Rubén
Aug 27, 2021 at 16:45
• Okay! I am slowly learning. Thank you, Ruben! Aug 27, 2021 at 17:07
• Possible duplicate: webapps.stackexchange.com/q/72835/88163 (try using FLATTEN and QUERY)
– Rubén
Aug 27, 2021 at 17:24
• That does solve the beef of my situation! Now I just need to figure out how to use a formula to filter out the results that are over 25 characters! Aug 27, 2021 at 17:57

``````=arrayformula(FILTER((unique(filter(FLATTEN(G2:AB),len(FLATTEN(G2:AB))))),len((unique(filter(FLATTEN(G2:AB),len(FLATTEN(G2:AB)))))) <= 25))