Top N Customers and Others

[prabhubox@yahoo.com]

Hi All,

I want to build a report to show the top 10 or top 5 customers plus the others as a single category in the Pivot table.

Is there any way in Pivot table to build this report. For your info i am using excel 2013 and do not have power pivot.

[alansidman]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[prabhubox@yahoo.com]

Attached for your reference.

[avk]

Ref attach file.
In column "D" mentioned number 1 to 10
In "E2"

Please Login or Register  to view this content.

In "F2"

Please Login or Register  to view this content.

Press SHIFT+CTRL+ENTER

[FlameRetired]

The only way I could do this in Pivot Table required some manual work.

1.) Add another item in the source table "All others" with this formula array entered.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

2.) Change the Data Source to include the additional item.

3.) Clear the existing filter (Top 5) from "Before" PT.

4.) Sort 'Sum of Amount' descending.

5.) Manually filter the first 5 (or N) items in 'Row Labels' and include "All others".

See the attached.