1

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

0

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.

  • this is ALMOST working but I think the issue is in the first step. It seems to be splitting by spaces instead of commas with each word now in its own cell. The final query is also giving a "formula parse error." I appreciate the step by step btw. Any advice on these issues? – J Hawke Jul 21 '17 at 21:14
  • Right, there should be one more argument of split, namely False. Added – user135384 Jul 21 '17 at 21:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.