1

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.

=ARRAYFORMULA(UNIQUE(TRIM(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(","&'Paste Raw Data here'!L2:L,,5000000)),",")))))

Output of the formula above

Appreciate any help.

0

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

=UNIQUE(TRANSPOSE(SPLIT(JOIN(",",A:A),",")))

enter image description here

| improve this answer | |
  • 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 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 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 at 6:43

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.