software

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.

PosPlayerPoints

1Tom124
2Linda122
3Harry107
4Jayne100
4Bob100
5Steve89
6Mark88
7John80
8Angie77
9Andrew71

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
RHYou can do this with a worksheet_change macro in the sheet module.
Right click on the sheet tabgt;view codegt;left window use worksheetgt;right
window select and write your code to sort.

--
Don Guillett
SalesAid Software

quot;RedHookquot; gt; wrote in message oups.com...
gt; Hi All
gt;
gt; I'm just getting up to speed with Excel and as an exercise I'm
gt; trying to implement a simple league table.
gt;
gt; What I'd like to know is how to create 'dynamically' a ranking
gt; table like the one shown below.
gt;
gt; Pos Player Points
gt;
gt; 1 Tom 124
gt; 2 Linda 122
gt; 3 Harry 107
gt; 4 Jayne 100
gt; 4 Bob 100
gt; 5 Steve 89
gt; 6 Mark 88
gt; 7 John 80
gt; 8 Angie 77
gt; 9 Andrew 71
gt;
gt; The table is sorted on the points column, the values for which are
gt; referenced from another sheet.
gt; Ideally I'd Like the table to be sorted automatically as players
gt; points total change(based on calculations In another sheet).
gt; Can I do this with formulas/macros or will it require some VBA code ?
gt;
gt; Thx
gt; RH
gt;
Private Sub Worksheet_Calculate()
With Me
.Columns(quot;A:Bquot;).Sort Key1:=Range(quot;B2quot;), _
Order1:=xlDescending, _
Key2:=Range(quot;A2quot;), _
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)

quot;RedHookquot; gt; wrote in message oups.com...
gt; Hi All
gt;
gt; I'm just getting up to speed with Excel and as an exercise I'm
gt; trying to implement a simple league table.
gt;
gt; What I'd like to know is how to create 'dynamically' a ranking
gt; table like the one shown below.
gt;
gt; Pos Player Points
gt;
gt; 1 Tom 124
gt; 2 Linda 122
gt; 3 Harry 107
gt; 4 Jayne 100
gt; 4 Bob 100
gt; 5 Steve 89
gt; 6 Mark 88
gt; 7 John 80
gt; 8 Angie 77
gt; 9 Andrew 71
gt;
gt; The table is sorted on the points column, the values for which are
gt; referenced from another sheet.
gt; Ideally I'd Like the table to be sorted automatically as players
gt; points total change(based on calculations In another sheet).
gt; Can I do this with formulas/macros or will it require some VBA code ?
gt;
gt; Thx
gt; RH
gt;
Maybe best to stop the cascade of events

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
With Me
.Columns(quot;A:Bquot;).Sort Key1:=Range(quot;B2quot;), _
Order1:=xlDescending, _
Key2:=Range(quot;A2quot;), _
Order2:=xlAscending, _
Header:=xlYes
End With
Application.EnableEvents = True
End Sub

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

quot;Bob Phillipsquot; gt; wrote in message
...
gt; Private Sub Worksheet_Calculate()
gt; With Me
gt; .Columns(quot;A:Bquot;).Sort Key1:=Range(quot;B2quot;), _
gt; Order1:=xlDescending, _
gt; Key2:=Range(quot;A2quot;), _
gt; Order2:=xlAscending, _
gt; Header:=xlYes
gt; End With
gt; End Sub
gt;
gt; 'This is worksheet event code, which means that it needs to be
gt; 'placed in the appropriate worksheet code module, not a standard
gt; 'code module. To do this, right-click on the sheet tab, select
gt; 'the View Code option from the menu, and paste the code in.
gt;
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove xxx from email address if mailing direct)
gt;
gt; quot;RedHookquot; gt; wrote in message
gt; oups.com...
gt; gt; Hi All
gt; gt;
gt; gt; I'm just getting up to speed with Excel and as an exercise I'm
gt; gt; trying to implement a simple league table.
gt; gt;
gt; gt; What I'd like to know is how to create 'dynamically' a ranking
gt; gt; table like the one shown below.
gt; gt;
gt; gt; Pos Player Points
gt; gt;
gt; gt; 1 Tom 124
gt; gt; 2 Linda 122
gt; gt; 3 Harry 107
gt; gt; 4 Jayne 100
gt; gt; 4 Bob 100
gt; gt; 5 Steve 89
gt; gt; 6 Mark 88
gt; gt; 7 John 80
gt; gt; 8 Angie 77
gt; gt; 9 Andrew 71
gt; gt;
gt; gt; The table is sorted on the points column, the values for which are
gt; gt; referenced from another sheet.
gt; gt; Ideally I'd Like the table to be sorted automatically as players
gt; gt; points total change(based on calculations In another sheet).
gt; gt; Can I do this with formulas/macros or will it require some VBA code ?
gt; gt;
gt; gt; Thx
gt; gt; RH
gt; gt;
gt;
gt;
Here's an option using formulas to create 'dynamically' the desired ranking
table

A sample construct is available at:
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.:

SnPlayerPoints
1John80
2Steve89
3Angie77
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=quot;quot;,quot;quot;,X!C2-ROW()/10^10)
(Leave D1 empty)

Select A22, 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,quot;gt;quot;amp;C2)-SUM((1/COUNTIF(
$C$2:$C$11,$C$2:$C$11))*($C$2:$C$11gt;C2)))

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
savefile.com/projects/236895
xdemechanik
---
quot;RedHookquot; wrote:
gt; Hi All
gt;
gt; I'm just getting up to speed with Excel and as an exercise I'm
gt; trying to implement a simple league table.
gt;
gt; What I'd like to know is how to create 'dynamically' a ranking
gt; table like the one shown below.
gt;
gt; Pos Player Points
gt;
gt; 1 Tom 124
gt; 2 Linda 122
gt; 3 Harry 107
gt; 4 Jayne 100
gt; 4 Bob 100
gt; 5 Steve 89
gt; 6 Mark 88
gt; 7 John 80
gt; 8 Angie 77
gt; 9 Andrew 71
gt;
gt; The table is sorted on the points column, the values for which are
gt; referenced from another sheet.
gt; Ideally I'd Like the table to be sorted automatically as players
gt; points total change(based on calculations In another sheet).
gt; Can I do this with formulas/macros or will it require some VBA code ?
gt;
gt; Thx
gt; 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
RHquot;RedHookquot; wrote:
gt; Thanks all for your input so far - I have the table sort working now.
gt;
gt; The final thing I'd like to do is to update the player ranking column
gt; dynamically once the table is sorted:

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

gt; But if you really insist on having a non-skip ranking
gt; (as indicated in your original post),
gt; we could replace the formula in A2
gt; with this complex array adapted from a past post by Daniel M:
gt;
gt; =RANK(C2,$C$2:$C$11)-(COUNTIF($C$2:$C$11,quot;gt;quot;amp;C2)-SUM((1/COUNTIF(
gt; $C$2:$C$11,$C$2:$C$11))*($C$2:$C$11gt;C2)))
gt;
gt; Then array-enter the formula in A2, i.e. press CTRL SHIFT ENTER
gt; (instead of just pressing ENTER), then copy A2 down to A11
gt;
gt; The above non-skip ranking is implemented in sheet: Y (2)
gt; in the sample book

--
Max
Singapore
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
RHJust 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)

quot;RedHookquot; gt; wrote in message ups.com...
gt; Thanks all for your input so far - I have the table sort working now.
gt;
gt; The final thing I'd like to do is to update the player ranking column
gt; dynamically once the table is sorted:
gt;
gt; Rank Player Points
gt; 1 Tom 124
gt; 2 Linda 122
gt; 3 Harry 107
gt; 4 Jayne 100
gt; 4 Bob 100
gt; 5 Steve 89
gt; 6 Mark 88
gt; 7 John 80
gt; 8 Angie 77
gt; 9 Andrew 71
gt;
gt; I guess the best approach is to name the two ranges of cells that
gt; comprise the Points and Rank columns. Then for each cell in the Points
gt; range compare the value with the previous one(unless it's the first),
gt; if the vaue is less then the corresponding cell in the Rank range gets
gt; set accordingly. If two players have the same points they are allocated
gt; the same Rank as for Jayne and Bob in the example above.
gt;
gt; What I'm not clear about is how to reference the individual cells in
gt; the named Points and Ranks ranges.
gt;
gt; Thx
gt; RH
gt;
No prob. On the vba approach, pl see Bob Phillips' response to your earlier
post in this thread.
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;RedHookquot; wrote:
gt; Hi Max
gt;
gt; Yes, you did cover that in your earlier post and thanks for your help.
gt; I'd just be interested to know how it would be done using the VBA
gt; approach as well.
gt;
gt; Regards
gt; RH