Find the top 10 from multiple groups.

[Selj]

I have spreadsheet similar to this:

Week 1 Week 2
Name Singles Handicap Singles Handicap %
Grp 1
Dave 23 23 17 18 81.0%
Jim 25 25 21 22 93.0%
Brad 14 12 18 18 62.0%
Moritz 20 13 22 21 76.0%
Bob 25 24 22 19 90.0%



Grp 2
Gary 16 19 23 23 81.0%
Tony 17 19 25 24 85.0%
Gary 19 19 14 12 64.0%
Frank 23 20 20 13 76.0%
Mike 22 19 25 24 90.0%

I need to know which 5 people have the top percentage. This is scaled down
version of the spreadsheet. There are a lot more groups and I need to keep
the space between the groups for other data. There are also more weeks (10).

I've tried lists but it won't let me use multiple selections.

 

[Ashish Mathur]

Hi,
Canoot make any sense from the data layout. Anyways, pivots have a feature
for Sort and Top 10

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Selj]

Hi,
Sorry, I guess the spacing all disappeared when I posted it. Not exactly
straight, but it might be easier to under stand...

Week 1 Week 2
Name Singles Handicap Singles Handicap %

Group 1
Dave 23 23 17 18 81.0%
Jim 25 25 21 22 93.0%
Brad 14 12 18 18 62.0%
Moritz 20 13 22 21 76.0%
Bob 25 24 22 19 90.0%

Group 2
Gary 16 19 23 23 81.0%
Tony 17 19 25 24 85.0%
Gary 19 19 14 12 64.0%
Frank 23 20 20 13 76.0%
Mike 22 19 25 24 90.0%

In the meantime I'll look into Pivots.

 

[Sean Timmons]

If you want top 5 total with ties (Not top 5 by group), highlight all columns
and place a Filter on. (Data > Filter>Autofilter). Select (Top 10...) on the
% column and select top 5 percent. This can return more than 5 rows if you
have multiple people with the same %.

Another option is to use a column to right, using =RANK(), if you'd like to
break out by group. Something like =RANK(F2,$F$2:$F$20) and drag down thr
group. Then, you can view only those with ranks up to 5 using a filter.

 

[Selj]

I can't just select the whole column. There is actually data in between the
groups that would skew the results. So, I need to select the % in the first
group then press control and select the second group and so on. When I try
to use a Filter it says can't filter data from multiple groups. When I try
and use Rank, it says too many arguements.

 

[Herbert Seidenberg]

Excel 2007 Pivot Table
No formulas needed.
http://www.mediafire.com/file/wimmwzi4utc/10_02_09.xlsx