Max,
The sheet you built is very impressive. I wouldn't have even come
close to that. My question for you now is: Is it possible to have it
so I have baseball, then 10 rows down or so, football and all the
information for everything shows up on the same page? I like the drop
down list idea, but it may not work for me. Please let me know your
thoughts when you can. Thanks.

Conor


Max wrote:
Here's a set-up using non-array formulas which provides the "Extract top xx
by game selected" functionality in a new sheet. The possibility of
ties/multiple ties in scores amongst the top xx is catered for in the set-up.

A sample construct is available at:
http://cjoint.com/?ivePYQz57f
Extract Top xx by Game in new sht.xls
(Link above is good for 2 weeks)

Assuming source data in sheet: X, cols A to C, data in row2 down. The key
cols are col A ie the game (Baseball, Football ..), and col C = scores.
Source data in X can be in any order (need not be sorted)

In another sheet: Y (say), place

In A2:
=IF(X!A2="","",IF(COUNTIF(X!$A$2:A2,X!A2)1,"",ROW ()))

In B2:
=IF(ROW(A1)COUNT(A:A),"",INDEX(X!A:A,MATCH(SMALL( A:A,ROW(A1)),A:A,0)))

In C2:
=IF(X!A2="","",IF(X!A2=$D$1,X!C2-ROW()/10^10,""))

Select A2:C2, copy down as far as required to cover the max expected extent
of data in X, say down to C9000? (Hide away cols A to C, or just format the
font in white to mask)

Click Insert Name Define, input:
Names in workbook: Game
Refers to: =OFFSET(Y!$B$2,,,SUMPRODUCT(--(Y!$B$2:$B$2000<"")))
Click OK

Then select D1, click Data Validation, Allow: List, Source: =Game
D1 will now yield a selectable dropdown of unique games from the key col A
in X

Enter the col headers in E1:F1 : Player, Score

Then place in E2:
=IF(ROW(A1)COUNT($C:$C),"",INDEX(X!B:B,MATCH(LARG E($C:$C,ROW(A1)),$C:$C,0)))

Copy E2 to F2, fill down to say F20 to cover the possibility of ties /
multiple ties in scores amongst the top 10. If there's absolutely no
possibility of ties in the scores for the top 10, then just fill down by 10
rows to F11

Test it out, select a game from the DV droplist in D1. The list of players
with the highest scores for the selected game will display in descending
order within the filled range E2:F15, all neatly bunched at the top. Just
read-off the top "10" as desired. In the event of tied scores, players and
their scores will be listed in the same relative order as they appear in X.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote:
I have 9000 rows of data and I would like to use the index function to
list the top 10 or first 10 values for each of my 35 categories. My
data is listed out as follows. (Please note that Player 1 for baseball
is not the same player 1 for football)

Baseball Player 1 5
Baseball Player 2 3
Baseball Player 3 2
Baseball Player 4 6
Baseball Player 5 1
Football Player 1 20
Football Player 2 30
Football Player 3 15
Football Player 4 18
Football Player 5 31

So then my hope is to have another sheet that draws from this data and
puts it in order while indexing and offsetting the following column -
not as big a deal. It should look something like this: (only top 3 for
this example)

Baseball
Player 4 6
Player 1 5
Player 2 3

Football Player 5 31
Player 2 30
Player 1 20


I have changed the data to keep it simple. I do not have 9000 rows of
this crap. As I mentioned, I would like to show the top 10
corresponding values. I can sort the data on the sheet to sort column
a and then column c to put everything in order from the beginning if it
is too difficult. I tried using the index function on the data and I
get the baseball value to get me what I want but then it gets to
football and can't do it because the part that changes the row not says
1:10, etc.

Any help would be greatly appreciated. I know a pivot table works for
this so please don't suggest that as a solution. Thanks for your help
in advance, whoever you are.