Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Leamington Spa, Warwickshire, England
    Posts
    136
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting Dynamically (2003)

    i have just been asked if you have two spreadsheets, one say for game results and the other a league table which is fed from the games results. Is it possible that the league table sheet can automatically sort so the the best player appear at the top of the list withiut having to A-Z?

    Thanks

    Michelle

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sorting Dynamically (2003)

    Check out some of the formulas in response to <post#=257175>post 257175</post#>

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Sorting Dynamically (2003)

    Hi,

    The thread mentioned by Steve looks good stuff if not requiring some effort to follow.

    I have done what you have mentioned reasonably simply by creating two tables - the first in some convenient static order, say alpha by player or team, and here all the calculations required are done, including the vital one of Ranking (using the RANK function). Then in the second table use the rank as the lookup function to select each cell required for each row by RANK from the first table. You should now have a ranked table which updates automatically.

    Good Luck!

    Peter Moran

  4. #4
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sorting Dynamically (2003)

    Hi petermoran,

    If 3 players got the same top score,

    lookup function return 1 player name only

    Any idea ?

    Regards

  5. #5
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Dynamically (2003)

    Bosco,
    In order to break ties, add a small random number to each calculation, i.e. a1+b1+rand()/100000000

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sorting Dynamically (2003)

    I prefer using ROW() instead of RAND(). It will keep a sort order the same each time. Using rand will change the order of the "dupes"

    Steve

  7. #7
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Dynamically (2003)

    I also have an excel worksheet that calclulates results and updates tables. Like Steve I use the Row() formula and I place a macro in the Worksheet _Activate tab so that the league table updates automatically when you click on the actual worksheet.
    My league table is in the range A1:M13. The first row is headers and so my league position 1 is in cell a2. Therefore my formula in cell a2 is "=Row()-1" to return 1 etc

    E.g

    Private Sub Worksheet_Activate()
    Range("a1:m13").Select
    Selection.Sort Key1:=Range("k1"), Order1:=xlDescending, Key2:=Range("m1") _
    , Order2:=xlDescending, Key3:=Range("e1"), Order3:=xlAscending, Header _
    :=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End Sub

    My league table sorts by 3 columns, points scored, then goal difference, and finally by no of games played, therefore 3 sort criteria are shown.

    hope this helps

    Robert

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Sorting Dynamically (2003)

    Hi,

    Sorry a bit slow getting back, but as has already been said the best way is to ensure the ranking factor is different on every line. What I have done is to rank on the sum of the points score (major rank) and then add in maybe the percentage (or the percentage / 100) (minor rank) which is often the factor which separates those ranked with the same points. This extra amount needs to be small enough so it does not give a false ranking due to the points + percentage changing the ranking, only ensuring the ranking value is different for each entry.

    Good Luck!

    Peter Moran

  9. #9
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting Dynamically (2003)

    A different formula system to compile a Top N list is shown in the attachment. The system itself is first published in:

    http://www.mrexcel.com/board2/viewtopic.php?t=66944

    The attachment is created in response to a Top N question in:

    http://"]http://216.92.17.166/board2/viewtopic.php?t=69970">http:// http://216.92.17.166/board2/viewtopic.php?t=69970[/url]

    The essential element of the system is that it will dynamically include all ties of the Nth value.
    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •