A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

League table automatic sort/update



 
 
Thread Tools Display Modes
  #1  
Old May 15th, 2006, 02:22 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default League table automatic sort/update

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

  #3  
Old May 15th, 2006, 02:40 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default League table automatic sort/update

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



  #4  
Old May 15th, 2006, 03:28 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default League table automatic sort/update

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





  #5  
Old May 16th, 2006, 07:48 AM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default League table automatic sort/update

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 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,""&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

  #6  
Old May 16th, 2006, 09:43 AM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default League table automatic sort/update

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

  #7  
Old May 16th, 2006, 10:44 AM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default League table automatic sort/update

"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
---
  #8  
Old May 16th, 2006, 11:23 AM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default League table automatic sort/update

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

  #9  
Old May 16th, 2006, 11:32 AM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default League table automatic sort/update

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



  #10  
Old May 16th, 2006, 07:01 PM posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: n/a
Default League table automatic sort/update

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Table problem Redwood Database Design 29 April 3rd, 2006 04:58 PM
Need to Improve Code Copying/Pasting Between Workbooks David General Discussion 1 January 6th, 2006 04:56 AM
Table Design A. Williams Database Design 3 April 29th, 2005 07:02 PM
Seeking some expert advice. HD87glide Using Forms 14 March 23rd, 2005 11:11 PM
Automatic filling of fields in table two from table one Jim Kelly Database Design 1 September 27th, 2004 10:16 PM


All times are GMT +1. The time now is 12:40 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.