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

Question

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!

Answer 1

So I believe I figured it out after parsing together some of the answers that I received for this one (thanks everyone):

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

This does all of what I was hoping for:

• It takes all data within the range and stacks it.
• It also gets rid of any blank columns
• Finally, it gets rid of any phrases over 25 characters long

I hope this helps others in need of an oddly specific solution!