Top 20 of Table

[Smurfit-Stone]

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

 

[PaddyD]

Is the top 20 based on single entries, or do multiple records per customer need to be summed before the ranking is determined?

 

[lenze]

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

 

[Aladin Akyurek]

For a formula approach see the set up in:

http://www.mrexcel.com/board2/viewtopic.php?p=993048

 

[PaddyD]

& for an advanced filter:

Book1
	A	B	C	D	E	F
1	ID	Num			ID	Num
2	a	1		FALSE	h	8
3	b	2			i	9
4	c	3			j	10
5	d	4
6	e	5
7	f	6
8	g	7
9	h	8
10	i	9
11	j	10
Sheet1

criteria range (in yellow) has criteria:

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

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

 

[Domski]

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.

 

[Smurfit-Stone]

You Guys are Awesome....Thanks so much