Pivot Table combining Categories into another column

[tazzmacd]

I am hoping somebody can help me here, my Raw Data is from a support org and has the Client service Manager, client names, Categories of work and hours worked in the raw data. What I need to do is create a pivot table that will look something like this

		Category A	Category B	Category C	Category D	Category E	other	total
Manager 1	Client 1	5,000	20	400	600	8,000	400	14,420
	Client 2	1,000.00	10.00	100.00	400.00	5,000.00	800.00	7,310
	Client 3	200.00	-	800.00	100.00	1,000.00	500.00	2,600
Manager 2	Client 4	300.00	50.00	50.00	800.00	10,000.00	100.00	11,300
	Client 5	800.00	80.00	400.00	400.00	400.00	200.00	2,280
	Client 6	1,500.00	500.00	600.00	1,500.00	600.00	300.00	5,000

<tbody>
</tbody>

This is simple enough to create but here is the catch, the Categories listed above are the top 5 of about 20 categories that the worked hours can fall into. What I need to do is to group all the other categories outside the top 5 to fall under the heading of Other in the pivot table. I then need the ability to expand this new category to show the subset that this Other column holds the totals for. So I cannot loose the Category names, I just need a way to combine them.

What I need to know is if this is possible? And if it is, how do I go about doing this? The spreadsheet will be updated on a monthly bases to bring in new YTD totals so I need this to be able to populate easily. I am currently using a Named range for the full table and it is working well but I need to add in the additional piece of the Other column.

Any help would be appreciated.

Thanks

Pat

 

[steve the fish]

Just use a helper column on the data. Call your top whatever categories their original name and the others as others. Pivot on that column rather than your original column.