We don’t require any plugin or Apps Script to unstack data in Google Sheets. All you want to use is my custom array formula.
Before beginning to unstack data to the corresponding groups in Google Sheets, let me explain the term ‘unstacking data’.
Example:
This screenshot explains stacking and unstacking.
Columns A and B contain the stacked data.
It is countries participating in a football tournament and their assigned group.
Column A contains the country names, and B the group they belong.
I’ve unstacked this data in columns D to K. Now the countries are put under their respective groups together.
How do we unstack data to group similarly above in Google Sheets?
Formula to Unstack Data to Groups in Google Sheets
You can use the below formula to unstack data to groups in Google Sheets.
=transpose(ArrayFormula(trim(sort(split(transpose(query({transpose(unique((B1:B)));ArrayFormula(IF(len(A1:A),IF(transpose(unique(B1:B))=B1:B,"-"&A1:A,""),""))},,1000)),"-")))))Update:- We can now use the Bycol function with Filter and easily unstack data to groups. You will get that formula at the end of this post.
In this formula, I’ve considered the stacked data range in A1:B, which means columns A and B, as per the screenshot above.
So you can use this formula in any other column. Here I am using this formula in cell C1.
If you want, you can limit (close) the stacked data range in this formula, i.e., A1:A to A1:A20 and B1:B to B1:B20.
But I’ve chosen open ranges because I want to unstack any newly added rows to respective groups without modifying the formula.
This formula is a combination of Google Sheets’ different functions.
You can refer to my Google Sheets Function Guide to find the tutorials for all the functions used in the above formula.
To give you some idea about the functioning of this formula, let me explain some parts of this unstacking formula for you.
You can skip the below tips and use the formula without much worrying.
Steps Involved
Have a look at the Cyan Blue highlighted part of the above formula.
That’s the essence of my formula for unstacking data to relevant groups in Google Sheets.
If you ask me how to Unstack data to Group in Google Sheets, I recommend you use the above-highlighted part. That would be enough!
Note # 1:
When you test part of a master formula, you may additionally need to use an Array Formula.
It’s because there may be an array formula somewhere in the master formula that applies to many individual parts in it.
It applies to our Cyan Blue highlighted part.
=ArrayFormula(IF(transpose(unique(B1:B20))=B1:B20,A1:A20,""))Note # 2:- I have used a closed range here as it’s just for testing.
Is this simple formula enough to unstack data in Google Sheets?
Yes! But it lacks the style part. The result will be as follows.
This formula does the unstacking but skips rows (leaves blank cells) in all columns.
You can see the use of the Query Function in the master formula (highlighted in Orange).
Let me explain the role of Query to unstack data to groups in Google Sheets.
I am using only the range A1:B8, two groups, for testing.
=query({transpose(unique((B1:B8)));ArrayFormula(IF(len(A1:A8),IF(transpose(unique(B1:B8))=B1:B8,"-"&A1:A8,""),""))},,1000)The Query formula combines the stacked data returned by the Cyan Blue colored part of the formula as below.
Result:
Group A -Russia -Saudi Arabia -Egypt -Uruguay
Group B -Portugal -Spain -Morocco -Iran
With the help of functions like Trim, Sort, Split, and Transpose, I’ve re-arranged the unstacked data and thus removed the skipping of rows.
See how the formula arranges a different set of data below.
In the first example, both columns in the stacked data contain text. Here the second column contains numbers.
In cell references D1:F4 you can see that the students are grouped by their rank.
Unstack Data Using BYCOL and FILTER in Google Sheets – New!
Google launched a couple of functions in Sheets in the later part of the year 2022.
Most of them are Lambda Helper Functions. Bycol is one among them.
We can use this function with Filter to unstack data to groups in Google Sheets. It requires two steps.
In the first step, unique the group column using the below formula.
=transpose(unique(B:B))In C2 (assume the above Unique is in cell C1), insert the below formula.
=bycol(C1:J1,lambda(r,filter(A1:A,B1:B=r)))Both the above formulas are array formulas. If there are values in the range it expands, it may return the #REF! error value.
Hi Prashanth!
Thank you for this wonderful resource!
I have some stacked data that is in 5 columns. Column headings are as follows:
Survey | Language | ID | Question | Value
Is there an expanded version of this formula that would work for me?
Many thanks!
Hi, Nate,
I don’t have a formula for that. I have a distantly related tutorial titled “How to Aggregate Strings Using Query in Google Sheets” which I have linked in one of my earlier replies.
Also, please see this tutorial.
Formula to Combine Duplicate Rows in Google Sheets [No-Addon]
See if that helps?
Best,
Hello. Thank you for your great article. I have tried the solution for a Google Sheet I am working on however it is not producing the results we hoped for. I have a list of attendees for different events. The columns are First Name, Last Name, Email, and Event.
The first three columns (Name, Last Name, Email) will contain the same information when someone signed up for a unique event. The data looks like this.
first name A | last name A | email A | Event Name 1
first name A | last name A | email A | Event Name 2
first name B | last name B | email A | Event Name 1
etc.
What we would like to do is have a single row with:
First Name, Last Name, Email, Event 1, Event, 2, etc. from our current stacked data.
First Name A | Last Name A | email A | Event Name 1 | Event Name 2
First Name B | Last Name B | email B | Event Name 1
Is this possible with Google Sheets?
Thank you in advance for your time.
Hi, Joe,
Welcome to Info Inspired!
The tutorial above is not the one that you may want to follow. Here is the proper one – How to Aggregate Strings Using Query in Google Sheets.
The tutorial contains the basic but only deals with two columns of data. Since you have more columns I have customized the formula for you. Here is that.
=ArrayFormula(query(query({A2:D,if(len(A2:A),row(A2:A)-match(A2:A&B2:B,A2:A&B2:B,0),)},"Select Col1,Col2,Col3, max(Col4) where Col1 is not null group by Col1,Col2,Col3 Pivot Col5"),"Select * offset 1",0))Make sure that you have sorted your data.
I am considering the data range A2:D (I am normally leaving the first row as it may contain field labels). So adjust your data accordingly.
Please do check the tutorial (link shared above) to understand this formula.
Best,