Getting the count and unique values of a column of comma separated values?

Question

Supposing all I have is the column A below

               +         +
     A         |    B    |    C
+--------------|---------|----------+
               |         |
  X, Y, Z      |   X     |     3
               |         |
  X, Z         |   Y     |     2
               |         |
  X, Y         |   Z     |     2
               +         +

How do I generate columns B and C - where the B column grabs the unique elements from A, and the C column generates a count of those values.

Answer 1

=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

Answer 2

Is it possible to create a hidden sheet?

If yes, 1) use the SPLIT() function to separate the values into columns and 2) use COUNTIF() on the hidden sheet to get the number of values

Answer 3

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".