:cool:Team Leaders

[justpete]

Hi, I have the following spreadsheet. All I'm trying to do is list the 3 top players from each column on a separate sheet. What's the simplest way to do that? I tried Advanced filtering, but always get an error. I also tried a number of the formula solutions on here, but haven't had any luck. Can anyone help?

/ps, hope this formatting works!

Name Total AB AB R H 1B 2B 3B HR RBI BB HB K SB SF AVG OBA SLG
Jim I 12 7 4 6 5 0 0 1 5 3 0 0 1 2 0.857 0.900 1.286
Arif P 16 15 5 10 9 0 0 1 8 1 0 1 0 0 0.667 0.688 0.867
Mike T 14 14 6 9 8 1 0 0 0 0 0 0 2 0 0.643 0.643 0.714
Mike F 16 15 3 7 5 2 0 0 1 1 0 4 2 0 0.467 0.500 0.600
Scott M 15 13 7 6 2 3 0 1 8 2 0 1 4 0 0.462 0.533 0.923
Paul M 13 11 0 5 4 1 0 0 3 1 1 0 1 0 0.455 0.538 0.545
Chris P 16 16 3 7 6 0 0 1 4 0 0 0 0 0 0.438 0.438 0.625
Dale W 16 13 4 5 3 0 0 2 6 3 0 2 0 0 0.385 0.500 0.846
Andy H 15 13 5 5 2 2 1 0 3 2 0 2 7 0 0.385 0.467 0.692
Ryan S 15 12 6 3 2 1 0 0 2 2 1 1 0 0 0.250 0.400 0.333
Peter F 7 5 1 1 1 0 0 0 1 2 0 3 0 0 0.200 0.429 0.200
Steve H 15 11 3 2 1 1 0 0 3 4 0 0 2 0 0.182 0.400 0.273
Paul B 14 12 3 2 2 0 0 0 1 2 0 3 0 0 0.167 0.286 0.167
Joe F 16 15 1 2 2 0 0 0 3 1 0 1 0 0 0.133 0.188 0.133
Red Sox Team 200 172 51 70 52 11 1 6 48 24 2 18 19 0.407 0.485 0.621

 

[justpete]

I have also tried the SQL query, but it needs to be a self contained file that I can send out and others can update...

 

[justpete]

hmm, ever get the feeling you're talking to yourself? hmm.. anywho, I managed to get it going pretty well.

This is what I'm using for the Number columns...

=LARGE('Red Sox Team Batting'!$B$2:$B$15,1)
=LARGE('Red Sox Team Batting'!$B$2:$B$15,2)
=LARGE('Red Sox Team Batting'!$B$2:$B$15,3)

Where 'Red Sox Team batting' is the first sheet with all the stats

and for the name column, I came up with this:

=INDEX('Red Sox Team Batting'!$A$1:$B$15,SMALL(IF('Red Sox Team Batting'!$B$2:$B$15=$B$2,ROW('Red Sox Team Batting'!$B$2:$B$15)),ROW(1:1)),1)
=INDEX('Red Sox Team Batting'!$A$1:$B$15,SMALL(IF('Red Sox Team Batting'!$B$2:$B$15=$B$2,ROW('Red Sox Team Batting'!$B$2:$B$15)),ROW(2:2)),1)
=INDEX('Red Sox Team Batting'!$A$1:$B$15,SMALL(IF('Red Sox Team Batting'!$B$2:$B$15=$B$2,ROW('Red Sox Team Batting'!$B$2:$B$15)),ROW(3:3)),1)



The only problem is, that for subsequent columns and stats, it seems to error out on #NUM!...

Not quite sure what the problem is. I've adjusted it for iteration... any ideas?

 

[lenze]

Hi Pete and welcome to the board!!

I'm sure we would love to help, but I don't know what you want. Maybe you can post a picture of an example SS showing what you want. The results you expect and your errors.

lenze

 

[TinaP]

You're not talking to yourself; I've been following this thread since you first posted. The only trouble is, I can't figure anything out to help you. I found this post, but I can't get it to work with your data.

 

[justpete]

Hi Pete and welcome to the board!!

I'm sure we would love to help, but I don't know what you want. Maybe you can post a picture of an example SS showing what you want. The results you expect and your errors.

lenze

So Say I have 8 Players, and there relevant stats:
Hits Walks
Bob 3 16
Ted 2 6
Ron 6 44
Greg 8 22


I want to be able, on another sheet, to list Leaders automatically, like this (/edit, it actually doesn't even need to be another sheet):

Walks
Ron 44
Greg 22
Bob 16

Hits
Greg 8
Ron 6
Bob 3


The errors I'm getting just say #NUM!. Tracing the error seems to point back to the worksheet I'm in, not the 'Red Sox Team Batting' sheet like I specified in the formula. The formulas are posted above.

Thanks in advance for any help you can offer, and even just for looking!

 

[justpete]

Despite transferring evreything to the same sheet, I still get the errors

 

[wsjackman]

in your names formulas they all refer to $B$2, I think they should be b2, b3, and b4 respectively.

if you name the different columns
you can simplify your formulas

for example names ='Red Sox Team Batting'!$A$2:$B$15
total at bats TAB ='Red Sox Team Batting'!$B$2:$B$15
at bats AB ='Red Sox Team Batting'!$C$2:$C$15

to get the TAB stats
in A2 enter TAB
in A3,A4 and A5 respectively enter
=index(names,small(if(indirect(A2)=b3,row(names)-1,100),1)
=index(names,small(if(indirect(A2)=b4,row(names)-1,100),1)
=index(names,small(if(indirect(A2)=b3,row(names)-1,100),1)

in B3,b4,b5 respectively enter
=large(indirect(A2),1)
=large(indirect(A2),2)
=large(indirect(A2),3)

to get the AB stats
in A7 enter AB
copy A3:B5 and paste into A8

do the same thing for all of the other stats.

this does not cover ties for example the top TAB are all 16
the fourth person with 16 would not be shown and the order of the people with sixteen would be based on where they are in the list

My computer crashed after I tried these out and I have not redone them. hopefully I remembered everything correctly

 

[Phox]

Named Ranges:
PLAYERS = $A$2:$A$15
STAT1 = $B$2:$B$15

This is an array formula. When you enter, you need to press Ctrl+Shift+Enter rather than just enter.

=INDEX(PLAYERS,MATCH(LARGE(STAT1+1/ROW(STAT1),ROW()-1),STAT1+1/ROW(STAT1),0))

(Note: formula needs to be modified if data does not start on Row 2)

 

[justpete]

How do I create a named range? I'm a n00b - this is my first time trying to do complex (seemingly) formulas in Excel.