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).
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)),",")))))
Appreciate any help.