2

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

3

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 ?

  • damn! that is AWESOME! thank you so much. i still have to nail down some other specifics but that might do it. – Bardo Oct 11 '15 at 17:32
  • Thank you, I will! Can I trouble you for one last bit of help. I have added some secondary data that accompanies each entry, which will simply need to be copied over. How do I that data over into the table you've created? – Bardo Oct 12 '15 at 12:50
  • Sure, take a look at cell G2 where you will find an amended formula that also 'copies' the extra data. If wanted the columns can be re-ordered. – JPV Oct 12 '15 at 13:30
  • Looks great, exactly what I needed. I'm trying to compare your original code with the new so I can carry over more than two columns if the need arises. I see you did a VLOOKUP with the following language at the end: 'Form Responses'!A2:C, {1, 2, 3}, 0). What will I need to adjust if I had four columns to carry over instead of just two? Thanks again- – Bardo Oct 12 '15 at 14:13
  • {1, 2, 3} are the columns that are returned: the first one up untill the third one. ;-) – JPV Oct 12 '15 at 16:11

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.