3

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

1
  • I’m voting to close this question because it relies on linked information to understand the question which is no longer available. OP can edit with missing details for reopening.
    – Blindspots
    Sep 13, 2023 at 14:47

1 Answer 1

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 ?

4
  • Sorry to bug, I ran into an issue with the formula. It's most likely my issue but I can't figure out why it's not returning like you had set it before. The sheet is here: docs.google.com/spreadsheets/d/… The problem is with the Sort tab. If you look at the far right of the Form Responses tab I have it consolidate responses at the end. The third line has given me issue no matter how I've entered values. The teacher's name is WH and yet on the sorted tab it returns the name of the teacher above. Any idea?
    – Bardo
    Oct 19, 2015 at 0:36
  • That's because you one referral level listed twice (see Form Responses!DE3:DE4). VLOOKUP will only return the first match it finds.
    – JPV
    Oct 19, 2015 at 6:39
  • Thanks again, you've been so helpful. Any idea how to workaround this? I will need multiple entries that have different individuals selected but with the same assignment
    – Bardo
    Oct 20, 2015 at 0:23
  • I added another help request for the timestamp to be pulled over. check it out if you get a chance: webapps.stackexchange.com/questions/86515/…
    – Bardo
    Nov 10, 2015 at 18:27

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