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.
Best Answer