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
<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
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