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

Improve this question

1 Answer 1

Reset to default
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 ?

Improve this answer
9
  • 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, 2015 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, 2015 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, 2015 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, 2015 at 14:13
  • {1, 2, 3} are the columns that are returned: the first one up untill the third one. ;-)
    – JPV
    Oct 12, 2015 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.