I'm trying to come up with a formula to consolidate information that's being dumped into a spreadsheet from a Google Form. The Form utilizes questions that repeat and allow a user to input multiple responses.

Google Form inputs example: Static Question/Answer A:C, then Questions D:F, Answers D:F, Question G(add another response?), if yes repeat Questions D:F as H:J with new answers, then question G repeated as question K (repeating, moving columns further right based on inputs), if answer is no submit form.

The Form dumps a lot of information but I want to consolidate all of those responses ignoring Question D and its response so that my spreadsheet consolidates all variations of the response for Questions D:F.

I apologize for how lengthy this question is, I will post images below to make it make sense.

The top image is the Google Form input and the Bottom Image is what I want my Sheet to look like after a formula is utilized. I just can't wrap my head around the Arrayfomula that would be needed.

What the Google Form Dump looks like

What I want it to look like

  • Consider adding a link to your sample spreadsheet, to give the volunteer contributors here some realistic data and a testing ground (though this type of question is asked often, and you should be able to find an answer if you search).
    – Erik Tyler
    Jul 9 at 3:17


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy