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:




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!


1 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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.