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

[TABLE="width: 764"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Category A[/TD]
[TD]Category B[/TD]
[TD]Category C[/TD]
[TD]Category D[/TD]
[TD]Category E[/TD]
[TD]other[/TD]
[TD]total[/TD]
[/TR]
[TR]
[TD]Manager 1[/TD]
[TD]Client 1[/TD]
[TD]5,000[/TD]
[TD]20[/TD]
[TD]400[/TD]
[TD]600[/TD]
[TD]8,000[/TD]
[TD]400[/TD]
[TD]14,420[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Client 2[/TD]
[TD]1,000.00[/TD]
[TD]10.00[/TD]
[TD]100.00[/TD]
[TD]400.00[/TD]
[TD]5,000.00[/TD]
[TD]800.00[/TD]
[TD]7,310[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Client 3[/TD]
[TD]200.00[/TD]
[TD]-[/TD]
[TD]800.00[/TD]
[TD]100.00[/TD]
[TD]1,000.00[/TD]
[TD]500.00[/TD]
[TD]2,600[/TD]
[/TR]
[TR]
[TD]Manager 2[/TD]
[TD]Client 4[/TD]
[TD]300.00[/TD]
[TD]50.00[/TD]
[TD]50.00[/TD]
[TD]800.00[/TD]
[TD]10,000.00[/TD]
[TD]100.00[/TD]
[TD]11,300[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Client 5[/TD]
[TD]800.00[/TD]
[TD]80.00[/TD]
[TD]400.00[/TD]
[TD]400.00[/TD]
[TD]400.00[/TD]
[TD]200.00[/TD]
[TD]2,280[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Client 6[/TD]
[TD]1,500.00[/TD]
[TD]500.00[/TD]
[TD]600.00[/TD]
[TD]1,500.00[/TD]
[TD]600.00[/TD]
[TD]300.00[/TD]
[TD]5,000
[/TD]
[/TR]
</tbody>[/TABLE]

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.