I was hoping to get some help and direction. I have two sheets--a 'Raw data' sheet and a 'Dashboard' sheet. A user would dump the raw data in the first sheet and the dashboard will summarise figures automatically (built some scripts for it).

In the 'Raw Data' sheet, there is a column for Teams which contain the names of the different teams a user is a part of (screenshot below).

Screenshot of raw data

Job to be done: I would like to get the list of unique teams from this column excluding the blank ones.

Challenge: I have this formula which works well. However, it includes the blank value which I want to exclude and I couldn't seem to figure out to adjust the formula. I included the output of the formula below. I tried rethinking the formula using JOIN or CONCATENATE but kept hitting the 50000 character limit error.


Output of the formula above

Appreciate any help.

2 Answers 2


By making use of SPLIT's default option to remove empty entries, I think your formula can be as simple as


enter image description here

  • Hey @Glorfindel, thanks for getting back to me. Much appreciated. The formula you suggested is awesome but I tried it with a bigger data set and got the error,"Text result of JOIN is longer than the limit of 50000 characters.".
    – gtanramon
    Jun 2, 2020 at 23:49
  • Bummer ... it might work with a more complicated formula like yours but I'm not too familiar. It might even be a Google Sheets formula isn't the right tool for the job and you'll need some JavaScript programming in the form of a Google Apps Script.
    – Glorfindel
    Jun 3, 2020 at 6:05
  • That is looking to be the case. No worries and thank you again. Appreciate the time you took out to help :)
    – gtanramon
    Jun 3, 2020 at 6:43

Instead of a blank second argument for QUERY use "select * where Col1 is not null". The resulting formula is:

=ARRAYFORMULA(UNIQUE(TRIM(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(","&'Paste Raw Data here'!L2:L,"select * where Col1 is not null",5000000)),",")))))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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