Google Forms – Simplify Checkbox Responses into Itemized List

google sheetsgoogle-forms

This is more of a Google Sheets question than a Forms question.
I want to receive form responses from checkbox questions (which unfortunately cannot be separated into separate rows) and flip the data so that responses start their own row on a separate tab.
Here is my basic spreadsheet.

The first tab is the form data I would collect (checkbox answers). Basically, it illustrates who needs to complete what assignment (if names need to be separated I can remove the commas from within the names, eg: Clinton Bill).

The second tab is what I'd like to have happen, with a list and the assignment they need to complete (preferably one per row if there are duplicates).
enter image description here

Best Answer

see cell E2 on your second tab where I entered:

=sort(ArrayFormula( {transpose(split(regexreplace(join(";", 'Form Responses'!C2:C), "(\w+),\s(\w+),\s", "$1, $2;"),";")),transpose(split(join("/", rept('Form Responses'!A2:A&"/", if(len('Form Responses'!C2:C), (len('Form Responses'!C2:C)-len(substitute('Form Responses'!C2:C, ",",""))+1)/2,)) ),"/"))}),1,1)

See if that helps ?

Related Topic