I’m working on a Google Sheets project where I need to generate a summary table from a dataset. The dataset includes columns for categories and subcategories, and I want to create a new table where:
- Each main category is listed.
- Each main category is followed by blank rows, with the number of blank rows equal to the number of subcategories under that main category.
- Subcategories are listed in the rows following the blank rows.
Example
Assume I have the following columns:
| Main Category | Subcategory |
|---|---|
| Food | Eating Out |
| Food | Dining |
| Health | Supplements |
| Health | Medications |
| Technology | Gadgets |
The output table should look something like this:
| Col. A | Col. B |
|---|---|
| Food | |
| Eating Out | |
| Dining | |
| Health | |
| Supplements | |
| Medications | |
| Technology | |
| Gadgets |
I have attempted to use functions such as ARRAYFORMULA, FLATTEN, and SEQUENCE, but encountered issues with generating the correct layout and alignment. I’ve also looked into this other question, but it’s quite complex to understand if it is easily adaptable to my case (I don’t have numbers to group by, as in that example).
Can anyone provide a solution or approach to achieve this layout in Google Sheets?
You may try:
<code>=reduce(tocol(,1),unique(tocol(A2:A,1)),lambda(a,c,ifna(vstack(a,c,hstack(,filter(B:B,A:A=c))))))
</code>
<code>=reduce(tocol(,1),unique(tocol(A2:A,1)),lambda(a,c,ifna(vstack(a,c,hstack(,filter(B:B,A:A=c))))))
</code>
=reduce(tocol(,1),unique(tocol(A2:A,1)),lambda(a,c,ifna(vstack(a,c,hstack(,filter(B:B,A:A=c))))))
1