How do I combine rows of data so that only some columns will have combined cells?
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!
Answers

Have you tried creating a report with Grouping?

I would create a second sheet that does the following, assuming sub categories are not duplicated across categories.
 Has a subcategory column with an index(distinct(collect())) lookup to the original sheet, for the index number you could just have a column with #1100 (so create 100 rows with the number values in the #number column) so your formula would be index(distinct(collect())), Number@row)
 The category column would just do a lookup for the category based on the subcategory. I usually do an index(collect()) for that too. The Collect would grab the category with the criteria being that the subcategory matches the subcategory @row in the second sheet
 Now you have a list of all your distinct subcategories and their main category, you can do an index(collect()) on Merchant and Product Specialist (using the subcategory 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.

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!
Help Article Resources
Categories
Check out the Formula Handbook template!