Unstack Multiple Form Responses in Google Sheets

Published on

With the title unstack multiple form responses in Google Sheets, I meant to say something like consolidating rows or merging rows.

Suppose, you are conducting different events and use a Google Form to collect data.

In case of multiple events and a single Form, you may want to allow multiple responses for each event.

Must Read: All that You Want to Know About Setting up of Google Docs Forms.

In this case, if you have connected your Form with Google Sheets, you may have multiple entries for the same person in your Sheets tab.

With the help of this tutorial, you can learn how to unstack multiple form responses in Google Sheets.

Here is one example for stacking multiple form responses in Google Docs Sheets.

Form Response in Sheet1:

unstack multiple event signup

Unstacked Multiple Form Responses in Sheet2:

Unstack Multiple Form Responses in Google Sheets

If this is not the type of unstacking that you are looking for, then you may check my below similar tutorials.

  1. How to Unstack Data to Group in Google Sheets Using Formula.
  2. The Formula to Combine Duplicate Rows in Google Sheets.

How to Unstack Multiple Form Responses in Google Sheets

In my sample data above (Form submission), the first four columns only contain duplicate data. So what we want to do is unique these columns and transpose the fifth column that is “Event Signed”.

But unfortunately using Unique and Transpose may not going to solve this problem.

To unstack multiple form responses in Google Sheets, we can use Query string aggregation.

Instead of going into each step in Query string aggregation here, I have detailed that in a separate tutorial. See that first – How to Aggregate Strings Using Query in Google Sheets.

In that, you can see one example with two column data and in that the first column contains the duplicates. Learn that formula thoroughly.

This tutorial is an extended version of the formula presented in that tutorial. Here I have more columns to unstack.

The Formula to Unstack Multiple Form Responses in Sheets

Here is my formula for the above data range A2:E. Please make sure that the data range is sorted using the menu, Data > Sort range.

You can include the Sort function within the below formula also. That detail you can find in my tutorial of which the link shared above.

=ArrayFormula(query(query({Sheet1!A2:E,if(len(Sheet1!A2:A),row(Sheet1!A2:A)-match(Sheet1!A2:A&Sheet1!B2:B,Sheet1!A2:A&Sheet1!B2:B,0),)},"Select Col1,Col2,Col3,Col4, max(Col5) where Col1 is not null group by Col1,Col2,Col3,Col4 Pivot Col6"),"Select * offset 1",0))

To know how to modify this unstacking formula for a different data, you may want to compare this formula with the below example and formula.

unstack three column stacked data

I am unstacking this three column Form response data using the below formula.

Unstacked three columns data in Google Sheets

Changes in Formulas and Reason for the Change:

Changes in Data Range:

Sheet1!A2:E (5 columns) changed to Sheet1!A2:C(3 columns).

Changes in Query Select Clause:

Select Col1,Col2,Col3,Col4, changed to Select Col1,Col2, – In the first data, the first four columns contain duplicates, i.e., First Name, Last Name, Tel, and Email Id.

In the second data it’s limited to two columns, i.e., First Name and Last Name.

Changes in Max Function:

The max(Col5) changed to max(Col3). Obviously, we want to transpose the “Event Signed” column and that column number is different in both the datasets.

Changes in Group by Clause:

The group by Col1,Col2,Col3,Col4 changed to group by Col1,Col2. We must use all the columns in the Select clause in the Group by clause too.

Changes in Pivot Clause:

In the first example the pivot column number is 6 and in the second it’s column 4. If you check the data you can see that there are only 5 columns in the first example and 3 columns in the second.

Actually, there is one virtual column in my formula at the end of the original data. That column number comes as the pivot column in the formula.

That’s it. Follow the above two examples to learn how to unstack multiple form responses in Google Sheets.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.