Sorting Dynamically (2003)

Topic

New Reply

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

Reply | Quote

Replies

Check out some of the formulas in response to post 257175

Steve

Reply | Quote

Check out some of the formulas in response to post 257175

Steve

Reply | Quote

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

Reply | Quote

Hi petermoran,

If 3 players got the same top score,

lookup function return 1 player name only

Any idea ?

Regards

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Hi petermoran,

If 3 players got the same top score,

lookup function return 1 player name only

Any idea ?

Regards

Reply | Quote

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

Reply | Quote

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%5B/url%5D

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

http://”%5Dhttp://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.

Reply | Quote

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%5B/url%5D

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

http://”%5Dhttp://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.

Reply | Quote