Multiple choice response in rows without repeating other entries

Question

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:

Can you help us in some way?

Answer 1

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.

Answer 2

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."