0

We have a multiple choice question in our Google Form.
We use some other spreadsheets to re-organize questions from the Google Form response sheets.

What we need is having multiple choice responses transposed into rows but leaving all the other information in the first reply row.

So far, using this formula we could retrieve the multiple choice into rows, but we need to get rid of the repeated information.

Here's the example, including a form example, our need and what we achieved so far:

enter image description here

Can you help us in some way?

1
  • Welcome to Web Applications. While it's very helpful to include links to posts in this site, questions should be self contained so please include the formula that you tried in the question body. – Rubén May 23 at 18:39
0

You can remove repeated values in the "What we have achieved so far" table you show above like this:

=array_constrain( arrayformula( if( column(Repeated!A2:E) = column(Repeated!E2:E), Repeated!E2:E, if( Repeated!A2:A <> offset(Repeated!A2:A, -1, 0), Repeated!A2:E, iferror(1/0) ) ) ), rows(Repeated!A2:E), columns(Repeated!A2:E) )

Choose Insert > New sheet and put the formula in cell A2 of the new sheet. The formula assumes that the "What we have achieved so far" table is in Repeated!A2:E.

2
0

I will assume here that your form intake sheet is named Form Responses 1, that your headers in that sheet are in Row 1, and that your incoming form data runs A2:E.

Based on this, add a new, blank sheet to the spreadsheet and place the following formula in A1:

=ArrayFormula(QUERY(IFERROR(TRIM(SPLIT(FLATTEN(FILTER(IF('Form Responses 1'!A:A="",,{'Form Responses 1'!A:A&"|"&'Form Responses 1'!B:B&"|"&'Form Responses 1'!C:C&"|"&'Form Responses 1'!D:D&"|"&REGEXEXTRACT('Form Responses 1'!E:E,"[^,]+"),TRIM("||||"&SPLIT(REGEXEXTRACT('Form Responses 1'!E:E&",","[^,]+,(.+)"),",",1,1))}),'Form Responses 1'!A:A<>"")),"|",1,0))),"Select * WHERE Col5 Is Not Null"))

This formula will take the headers and data directly from the Form Responses 1 sheet and create the requested report.

Obviously, if your form-intake sheet is named something else, replace 'Form Responses 1' throughout the formula with your actual form-intake sheet name.

You will need to select the produced "Date" column and use Format > Number to set your desired date format for that column (which will initially show up as the raw date values of numbers in the 40000 range).

If your international locale is one that uses semicolons in place of commas, you will need this version:

=ArrayFormula(QUERY(IFERROR(TRIM(SPLIT(FLATTEN(FILTER(IF('Form Responses 1'!A:A="";;{'Form Responses 1'!A:A&"|"&'Form Responses 1'!B:B&"|"&'Form Responses 1'!C:C&"|"&'Form Responses 1'!D:D&"|"&REGEXEXTRACT('Form Responses 1'!E:E;"[^,]+")\TRIM("||||"&SPLIT(REGEXEXTRACT('Form Responses 1'!E:E&",";"[^,]+,(.+)");",";1;1))});'Form Responses 1'!A:A<>""));"|";1;0)));"Select * WHERE Col5 Is Not Null"))

As for how it works, let's first make sure that it does work for you as expected. If so, report back, and then I will get into the "how."

1
  • Erik, I also tried your suggestion and it worked as well. Thanks a bunch for both ideas! – Marco Martelli May 24 at 15:30

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.