Hi All

I'm just getting up to speed with Excel and as an exercise I'm
trying to implement a simple league table.

What I'd like to know is how to create 'dynamically' a ranking
table like the one shown below.

Pos Player Points

1 Tom 124
2 Linda 122
3 Harry 107
4 Jayne 100
4 Bob 100
5 Steve 89
6 Mark 88
7 John 80
8 Angie 77
9 Andrew 71

The table is sorted on the points column, the values for which are
referenced from another sheet.
Ideally I'd Like the table to be sorted automatically as players
points total change(based on calculations In another sheet).
Can I do this with formulas/macros or will it require some VBA code ?

Thx
RH

You can do this with a worksheet_change macro in the sheet module.
Right click on the sheet tabview codeleft window use worksheetright
window select and write your code to sort.

--
Don Guillett
SalesAid Software

"RedHook" wrote in message
oups.com...
Hi All

I'm just getting up to speed with Excel and as an exercise I'm
trying to implement a simple league table.

What I'd like to know is how to create 'dynamically' a ranking
table like the one shown below.

Pos Player Points

1 Tom 124
2 Linda 122
3 Harry 107
4 Jayne 100
4 Bob 100
5 Steve 89
6 Mark 88
7 John 80
8 Angie 77
9 Andrew 71

The table is sorted on the points column, the values for which are
referenced from another sheet.
Ideally I'd Like the table to be sorted automatically as players
points total change(based on calculations In another sheet).
Can I do this with formulas/macros or will it require some VBA code ?

Thx
RH

Private Sub Worksheet_Calculate()
With Me
.Columns("A:B").Sort Key1:=Range("B2"), _
Order1:=xlDescending, _
Key2:=Range("A2"), _
Order2:=xlAscending, _
Header:=xlYes
End With
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"RedHook" wrote in message
oups.com...
Hi All

I'm just getting up to speed with Excel and as an exercise I'm
trying to implement a simple league table.

What I'd like to know is how to create 'dynamically' a ranking
table like the one shown below.

Pos Player Points

1 Tom 124
2 Linda 122
3 Harry 107
4 Jayne 100
4 Bob 100
5 Steve 89
6 Mark 88
7 John 80
8 Angie 77
9 Andrew 71

The table is sorted on the points column, the values for which are
referenced from another sheet.
Ideally I'd Like the table to be sorted automatically as players
points total change(based on calculations In another sheet).
Can I do this with formulas/macros or will it require some VBA code ?

Thx
RH

Maybe best to stop the cascade of events

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
With Me
.Columns("A:B").Sort Key1:=Range("B2"), _
Order1:=xlDescending, _
Key2:=Range("A2"), _
Order2:=xlAscending, _
Header:=xlYes
End With
Application.EnableEvents = True
End Sub

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Bob Phillips" wrote in message
...
Private Sub Worksheet_Calculate()
With Me
.Columns("A:B").Sort Key1:=Range("B2"), _
Order1:=xlDescending, _
Key2:=Range("A2"), _
Order2:=xlAscending, _
Header:=xlYes
End With
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"RedHook" wrote in message
oups.com...
Hi All

I'm just getting up to speed with Excel and as an exercise I'm
trying to implement a simple league table.

What I'd like to know is how to create 'dynamically' a ranking
table like the one shown below.

Pos Player Points

1 Tom 124
2 Linda 122
3 Harry 107
4 Jayne 100
4 Bob 100
5 Steve 89
6 Mark 88
7 John 80
8 Angie 77
9 Andrew 71

The table is sorted on the points column, the values for which are
referenced from another sheet.
Ideally I'd Like the table to be sorted automatically as players
points total change(based on calculations In another sheet).
Can I do this with formulas/macros or will it require some VBA code ?

Thx
RH

Here's an option using formulas to create 'dynamically' the desired ranking
table

A sample construct is available at:
http://www.savefile.com/files/4859486
Auto extract full descending sort n rank w_wo skips.xls

Assuming source data is housed in sheet: X, within A1:C11,
headers in A1:C1 , data from row2 to row11, viz.:

Sn Player Points
1 John 80
2 Steve 89
3 Angie 77
etc

In another sheet: Y (say)
With the same col headers in A1:C1

Put in A2: =RANK(C2,$C$2:$C$11)
Put in B2: =INDEX(X!B:B,MATCH(LARGE($D:$D,ROW(A1)),$D:$D,0))
Copy B2 to C2

Put in D2: =IF(X!C2="","",X!C2-ROW()/10^10)
(Leave D1 empty)

Select A2:D2, copy down to D11

A1:C11 auto-returns a full descending sort of the source table in X, sorted
by the points col. Players with tied points, if any, will appear in the same
relative order that they appear within the source table.

(Col D is a helper col with an arb tie-breaker for a full descending sort.
If desired, just hide it away)

The ranking within col A uses a simple RANK formula which gives duplicate
numbers the same rank, and will then skip accordingly subsequent ranks.
This simple rendition should suffice ?

But if you really insist on having a non-skip ranking
(as indicated in your original post),
we could replace the formula in A2
with this complex array adapted from a past post by Daniel M:

=RANK(C2,$C$2:$C$11)-(COUNTIF($C$2:$C$11,""&C2)-SUM((1/COUNTIF(
$C$2:$C$11,$C$2:$C$11))*($C$2:$C$11C2)))

Then array-enter the formula in A2, i.e. press CTRL+SHIFT+ENTER
(instead of just pressing ENTER), then copy A2 down to A11

The above non-skip ranking is implemented in sheet: Y (2)
in the sample book
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"RedHook" wrote:
Hi All

I'm just getting up to speed with Excel and as an exercise I'm
trying to implement a simple league table.

What I'd like to know is how to create 'dynamically' a ranking
table like the one shown below.

Pos Player Points

1 Tom 124
2 Linda 122
3 Harry 107
4 Jayne 100
4 Bob 100
5 Steve 89
6 Mark 88
7 John 80
8 Angie 77
9 Andrew 71

The table is sorted on the points column, the values for which are
referenced from another sheet.
Ideally I'd Like the table to be sorted automatically as players
points total change(based on calculations In another sheet).
Can I do this with formulas/macros or will it require some VBA code ?

Thx
RH

Thanks all for your input so far - I have the table sort working now.

The final thing I'd like to do is to update the player ranking column
dynamically once the table is sorted:

Rank Player Points
1 Tom 124
2 Linda 122
3 Harry 107
4 Jayne 100
4 Bob 100
5 Steve 89
6 Mark 88
7 John 80
8 Angie 77
9 Andrew 71

I guess the best approach is to name the two ranges of cells that
comprise the Points and Rank columns. Then for each cell in the Points
range compare the value with the previous one(unless it's the first),
if the vaue is less then the corresponding cell in the Rank range gets
set accordingly. If two players have the same points they are allocated
the same Rank as for Jayne and Bob in the example above.

What I'm not clear about is how to reference the individual cells in
the named Points and Ranks ranges.

Thx
RH

"RedHook" wrote:
Thanks all for your input so far - I have the table sort working now.

The final thing I'd like to do is to update the player ranking column
dynamically once the table is sorted:

But wasn't the part above covered in my earlier response,

But if you really insist on having a non-skip ranking
(as indicated in your original post),
we could replace the formula in A2
with this complex array adapted from a past post by Daniel M:

=RANK(C2,$C$2:$C$11)-(COUNTIF($C$2:$C$11,""&C2)-SUM((1/COUNTIF(
$C$2:$C$11,$C$2:$C$11))*($C$2:$C$11C2)))

Then array-enter the formula in A2, i.e. press CTRL+SHIFT+ENTER
(instead of just pressing ENTER), then copy A2 down to A11

The above non-skip ranking is implemented in sheet: Y (2)
in the sample book

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Hi Max

Yes, you did cover that in your earlier post and thanks for your help.
I'd just be interested to know how it would be done using the VBA
approach as well.

Regards
RH

No prob. On the vba approach, pl see Bob Phillips' response to your earlier
post in this thread.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"RedHook" wrote:
Hi Max

Yes, you did cover that in your earlier post and thanks for your help.
I'd just be interested to know how it would be done using the VBA
approach as well.

Regards
RH

Just add a ws function of

=RANK(C2,scores)

where scores is the named range of scores. Don't do that bit in VBA.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"RedHook" wrote in message
ups.com...
Thanks all for your input so far - I have the table sort working now.

The final thing I'd like to do is to update the player ranking column
dynamically once the table is sorted:

Rank Player Points
1 Tom 124
2 Linda 122
3 Harry 107
4 Jayne 100
4 Bob 100
5 Steve 89
6 Mark 88
7 John 80
8 Angie 77
9 Andrew 71

I guess the best approach is to name the two ranges of cells that
comprise the Points and Rank columns. Then for each cell in the Points
range compare the value with the previous one(unless it's the first),
if the vaue is less then the corresponding cell in the Rank range gets
set accordingly. If two players have the same points they are allocated
the same Rank as for Jayne and Bob in the example above.

What I'm not clear about is how to reference the individual cells in
the named Points and Ranks ranges.

Thx
RH