0

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!

4
  • 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

1 Answer 1

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 that you have read and understand our privacy policy and code of conduct.

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