How do I combine rows of data so that only some columns will have combined cells?

Cameron Ostafin

We have a pagination outline sheet for a book we create each year and each row has a different page number. A lot of the rows will have repeated information (i.e. Category and Sub Category) but the page number will be different each time. I want to combine all the rows when the data repeats for Category and Sub Category but update the Page Number cell to include all the page numbers for all the rows that are being combined for that given category and sub category.

So right now the data looks like this...

But I want it to look something like this...

Thanks!

Mike TV

@Cameron Ostafin

Have you tried creating a report with Grouping?

ericncarr

I would create a second sheet that does the following, assuming sub categories are not duplicated across categories.

1. Has a sub-category column with an index(distinct(collect())) lookup to the original sheet, for the index number you could just have a column with #1-100 (so create 100 rows with the number values in the #number column) so your formula would be index(distinct(collect())), Number@row)
2. The category column would just do a lookup for the category based on the sub-category. I usually do an index(collect()) for that too. The Collect would grab the category with the criteria being that the sub-category matches the sub-category @row in the second sheet
3. Now you have a list of all your distinct sub-categories and their main category, you can do an index(collect()) on Merchant and Product Specialist (using the sub-category again as the key), then just doing a join(collect()) on the page number to gather all the page numbers together in the Page Number column.

Cameron Ostafin

Thanks for the response Eric! I'm having trouble getting started with your direction, still a beginner with smartsheet. Any chance you could breakdown the steps a little more for me? Thanks for the help!