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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 123 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!