Top 20 of Table

Smurfit-Stone

Active Member
Joined
Dec 1, 2004
Messages
485
Hello Board,
I have a file that each month sales data is entered, there is a summary sheet in the same file that I display the top 20 customers, as well as another that list the top 10 based on sales $. Of course this top 20 list changes every month. What I was wondering is....is there a way or formula that would look at the hundreds of rows of data on the sales sheet and place only the top 20 on the summary sheet. I've tried the auto filter, but I have lots of columns of data on the sales sheet, and I only want certain columns to appear on the summary sheet. My thinking may be off, but I thought excel might be able to do this automatically. Thanks in advance :biggrin:
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Is the top 20 based on single entries, or do multiple records per customer need to be summed before the ranking is determined?
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
A Pivot Table will do this nicely. Set it up and use Field Settings>Advanced feature. Activate Top 10 AutoShow . Change the 10 as desired (say 20). Should give you exactly what you want.

lenze
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

& for an advanced filter:
Book1
ABCDEF
1IDNumIDNum
2a1FALSEh8
3b2i9
4c3j10
5d4
6e5
7f6
8g7
9h8
10i9
11j10
Sheet1


criteria range (in yellow) has criteria:

=B2>=LARGE($B$2:$B$11,3)

...change 3 to 20 or whatever...
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Also worth knowing that the Top 10 option on Autofilter is not only limted to the top 10 when you select and is quite flexible.
 

Forum statistics

Threads
1,136,926
Messages
5,678,605
Members
419,775
Latest member
joh93

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top