Graphing the top ten - Should be an easy one

[SteveD]

Good morning,

I'm creating a graph from a pivot table in Excel 07 and I've found the Top 10 filter. It works as it should, but how can I have Excel show me the Top 10 plus all the rest of my data combined into a filed something like "Other"?

Something like:
1 - 20
2 - 19
3 - 17
...
10 - 5
Other - 23

I tried to search for this... but did not even know what to search for.

Thanks,
Steve

 

[SteveD]

Have I stumped everyone? Is this not something that Excel can do?

Thanks Steve

 

[tusharm]

See
A 'Top 10' list with a consolidated Others category
http://www.tushar-mehta.com/publish_train/xl_vba_cases/0105_Top 10 with Consolidated Others.htm

 

[SteveD]

Very nice.
It will take some coding on the VBA side to make it work out, but this had a few tricks that are helpful.

Thanks!
Steve

 

[tusharm]

Thank you.

If you need help with the VBA part, the easiest approach would be to turn on the macro recorder, then go through the tutorial, and turn off the recorder. You will almost certainly have to clean up and generalize the Excel-generated code but you should have the core code.