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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
League table automatic sort/update
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 |
#3
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |