How to split and transpose text in cells from multiple rows into 1 column

Question

I have an automatically generating list of rows with multiple text items within them separated by a , (ColA). I'm trying to automatically split and transpose each row, where all responses end up in the same column B.

Bonus if the repeating text items in Column A could only be listed once in Column B along with a count of their occurrences in Column C. No worries if that bit isn't possible though.

Example sheet here: https://docs.google.com/spreadsheets/d/1Gelwp2eSFNHIc9pZLW2YwnoKHxid3faKrXHWDxYZcZI/edit?usp=sharing

I found a similar question here that was answered with a script, but I couldn't figure out how to fit it to my purpose.
https://stackoverflow.com/questions/17530499/how-to-split-and-transpose-results-over-2-columns

Answer 1

I'll describe the solution step by step:

Get all names in separate cells

=split(join(", ", filter(A2:A, len(A2:A))), ", ", False)

does this, by taking nonempty cells in the range A2:A (this is what filter is for), joining them using the same comma-space separator you have within cells, and then splitting by that separator (not by space or comma separately).

Transpose

This is easy: just add transpose.

=transpose(split(join(", ", filter(A2:A, len(A2:A))), ", ", False))

Get unique names only

This is only if you don't want the counts. unique does the job then.

=unique(transpose(split(join(", ", filter(A2:A, len(A2:A))), ", ", False)))

Get unique names and their counts

For this I would use query instead of unique:

=query(transpose(split(join(", ", filter(A2:A, len(A2:A))), ", ", False)), 
       "select Col1, count(Col1) group by Col1", 0)

Which says: group by name, and include the count of repetitions. The last argument "0" says the data has no header row, which it doesn't since we started with A2.