Do you find yourself struggling to consolidate rows or merge rows in Google Sheets? If you use a Google Form to collect data for multiple events, you may encounter the issue of having multiple entries for the same person in your Sheets tab. But fear not! In this tutorial, I will show you how to unstack multiple form responses in Google Sheets.
How to Unstack Multiple Form Responses in Google Sheets
Let’s dive right into the process of unstacking multiple form responses in Google Sheets. In the sample data example above (Form submission), the first four columns contain duplicate data. Our goal is to unique these columns and transpose the fifth column, which represents the “Event Signed” information.
Unfortunately, using the Unique and Transpose functions alone may not solve this problem. However, we can leverage a powerful function called Query string aggregation.
To fully understand the steps involved in Query string aggregation, I recommend checking out my detailed tutorial on “How to Aggregate Strings Using Query in Google Sheets.” There, you will find an example with two column data where the first column contains duplicates. Make sure you grasp the formula presented in that tutorial before proceeding.
Now, let’s get into the extended version of the formula for unstacking multiple form responses with more columns involved.
The Formula to Unstack Multiple Form Responses in Sheets
Here is the formula you can use for a data range of A2:E:
=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))You can also include the Sort function within the formula if you need to sort the data range. For more details on this, refer to my tutorial mentioned above.
To understand how to modify this unstacking formula for different data, let’s compare it with the following example and formula.
In this case, we are unstacking three columns of Form response data using the following formula:
=ArrayFormula(query(query({Sheet1!A2:C,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 where Col1 is not null group by Col1,Col2 Pivot Col3"),"Select * offset 1",0))Let’s break down the changes in the formulas and the reasons behind them:
- 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 example, the first four columns contain duplicates, including First Name, Last Name, Tel, and Email Id. However, in the second example, the duplicates are limited to only two columns, First Name and Last Name.
- Changes in Max Function: The max(Col5) changed to max(Col3). We want to transpose the “Event Signed” column, which has a different column number in both datasets.
- Changes in Group by Clause: The group by Col1,Col2,Col3,Col4 changed to group by Col1,Col2. To ensure accuracy, all columns included in the Select clause must also be included in the Group by clause.
- Changes in Pivot Clause: The pivot column number differs between the two examples. In the first example, it is column 6, whereas in the second example, it is column 4. The pivot column number is determined by the number of columns in the original data.
And there you have it! By following the two examples mentioned above, you can easily unstack multiple form responses in Google Sheets.
Don’t let the complexity of data overwhelm you. With the right formula and a little bit of know-how, you can effectively unstack form responses to gain valuable insights and make data-driven decisions.
For more Google Sheets tips, tricks, and tutorials, visit Crawlan.com. Unleash your spreadsheet superpowers and conquer any data challenge that comes your way!
