Sort by top ten, then all the rest

[sidcrenwelge]

In a pivot table, it is easy to apply a filter that shows the top 10 results. But what if I just want to organize the table so that the top ten results are shown on the first ten lines, then all the other rows are consolidated and summed into an 11th line called "Other." Thanks in advance.

 

[James006]

Hi,

First step is to add a new field in your database which will show the actual rank ... if the rank is between 1 and 10 and display "Other" for the rest ...

Second step is to insert a pivot table using this new field ...

 

[sidcrenwelge]

Thanks, James006. Good idea. In this case, the data set is fixed. The entire data set is being downloaded from an accounting package everyday into a tab in Excel, say Tab 1. In Tab 2, I've set up a pivot table to act upon that raw data. I was hoping to automate this so that I wouldn't need to manipulate the data at all. Any other thoughts?

 

[James006]

Hi,

If you are not allowed/authorized to alter Tab1 ... you could have Tab2 as a copy of Tab1 along with your manipulation ... and Tab3 with your Pivot table ..