Getting the count and unique values of a column of comma separated values?
Without hidden cells is possible to do it with an alternative method than the one proposed by Adam (that did not work in my case). I have tested it with google spreadsheets (from data coming from a google form using multiple selection answers):
=UNIQUE(TRANSPOSE(SPLIT(JOIN(", ";A2:A);", ";FALSE)))
Explanation goes as follows:
- JOIN to mix all the values from the A column (except A1 that could be the header of the column, if not, substitute it by A:A) separated by a coma
- SPLIT to separate all the mixed values by their comas
- TRANSPOSE to transformate the column into rows and viceversa
- UNIQUE to avoid repeated values
Take into account that my "," coma includes and space character i.e., ", " to avoid incorrect unique values because "Z" y not equal to " Z".
=ArrayFormula(QUERY(TRANSPOSE(SPLIT(JOIN(",",A:A),",")&{"";""}),"select Col1, count(Col2) group by Col1 label count(Col2) ''",0))
QUERY function
TRANSPOSE function
SPLIT function
JOIN function