Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with sorting lists of data.
I need to sort the following list of lap times so that all the riders who
have come in after the 2:00:00 mark are sorted in terms of number of laps then finishing time. Then the remaining riders who have pulled out before the 2:00:00 mark need to be sorted the same way. Based on the example below the list of rider numbers should end up in the order 405, 411, 401 402 and 409. I have included the cell references as well just in case they are required. I need to do it using vb code so that I can run it from a command buttonl. Thank you for any suggestions. H I J K L M Rider Laps Lap1 lap2 lap3 lap4 No. 5 401 4 0:37:49 1:09:48 1:43:27 1:58:48 6 405 4 0:38:49 1:11:20 1:42:57 2:16:14 7 402 3 0:40:36 1:17:04 1:59:38 8 411 3 0:44:03 1:21:27 2:04:01 9 409 2 0:38:38 1:10:47 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with sorting lists of data.
Hi "NDBC"
Normal sort will not work to find the rankings..You will have to put weightage for the number of laps as well as the minimum time covered. The below macro which uses Col N as a helper column..Adjust the starting row variable lngSRow to suit your requirement....Try and feedback Sub RankEntries() Dim lngRow As Long, lngSRow As Long lngSRow = 5 lngRow = lngSRow Application.ScreenUpdating = False Do While Range("H" & lngRow) < "" Range("N" & lngRow) = "=I" & lngRow & _ "+(HOUR(MAX(J" & lngRow & ":M" & lngRow & "))*2)" lngRow = lngRow + 1 Loop Range("H" & lngSRow & ":N" & lngRow - 1).Sort Key1:=Range("N" & _ lngSRow), Order1:=xlDescending, Orientation:=xlTopToBottom Range("N" & lngSRow & ":N" & lngRow - 1).ClearContents Application.ScreenUpdating = True End Sub If this post helps click Yes --------------- Jacob Skaria "NDBC" wrote: I need to sort the following list of lap times so that all the riders who have come in after the 2:00:00 mark are sorted in terms of number of laps then finishing time. Then the remaining riders who have pulled out before the 2:00:00 mark need to be sorted the same way. Based on the example below the list of rider numbers should end up in the order 405, 411, 401 402 and 409. I have included the cell references as well just in case they are required. I need to do it using vb code so that I can run it from a command buttonl. Thank you for any suggestions. H I J K L M Rider Laps Lap1 lap2 lap3 lap4 No. 5 401 4 0:37:49 1:09:48 1:43:27 1:58:48 6 405 4 0:38:49 1:11:20 1:42:57 2:16:14 7 402 3 0:40:36 1:17:04 1:59:38 8 411 3 0:44:03 1:21:27 2:04:01 9 409 2 0:38:38 1:10:47 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with sorting lists of data.
I thought we solved this problem at the beginning of July. Try this code.
The blanks should sort to the botoom of the table since we are sorting the times descending. If not, I wrote a 2nd macro that puts N/A in the blank cells to force them to the end. This si the same solution you eventually worked out. Sub SortTable1() LastRow = Range("H" & Rows.Count).End(xlUp).Row Col_J = 9 Col_M = 13 For ColCount = Col_J To Col_M Set Key2 = Cells(5, ColCount) Rows("5:" & LastRow).Sort _ key1:=Range("I5"), _ order1:=xlDescending, _ Key2:=Key2, _ order2:=xlAscending Next ColCount End Sub Sub SortTable2() LastRow = Range("H" & Rows.Count).End(xlUp).Row Col_J = 9 Col_M = 13 'fill in empty cells in table For RowCount = 5 To LastRow For ColCount = Col_J To Col_M If Cells(RowCount, ColCount) = "" Then Cells(RowCount, ColCount) = "N/A" End If Next ColCount Next RowCount For ColCount = Col_J To Col_M Set Key2 = Cells(5, ColCount) Rows("5:" & LastRow).Sort _ key1:=Range("I5"), _ order1:=xlDescending, _ Key2:=Key2, _ order2:=xlAscending Next ColCount End Sub "NDBC" wrote: I need to sort the following list of lap times so that all the riders who have come in after the 2:00:00 mark are sorted in terms of number of laps then finishing time. Then the remaining riders who have pulled out before the 2:00:00 mark need to be sorted the same way. Based on the example below the list of rider numbers should end up in the order 405, 411, 401 402 and 409. I have included the cell references as well just in case they are required. I need to do it using vb code so that I can run it from a command buttonl. Thank you for any suggestions. H I J K L M Rider Laps Lap1 lap2 lap3 lap4 No. 5 401 4 0:37:49 1:09:48 1:43:27 1:58:48 6 405 4 0:38:49 1:11:20 1:42:57 2:16:14 7 402 3 0:40:36 1:17:04 1:59:38 8 411 3 0:44:03 1:21:27 2:04:01 9 409 2 0:38:38 1:10:47 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with sorting lists of data.
Jacob,
Yet again you've given me another great idea. We are not quite there yet though. With your system if there are two riders who both finish on the same lap and go over the 2:00:00 mark they end up with the same rank. The concept is great though and all I need is a bit more thought into fine tuning the weighted combination and I'll be there. What does turning the screen updating do. Does this just make it run faster as it is not constantly changing things as it goes. "Jacob Skaria" wrote: Hi "NDBC" Normal sort will not work to find the rankings..You will have to put weightage for the number of laps as well as the minimum time covered. The below macro which uses Col N as a helper column..Adjust the starting row variable lngSRow to suit your requirement....Try and feedback Sub RankEntries() Dim lngRow As Long, lngSRow As Long lngSRow = 5 lngRow = lngSRow Application.ScreenUpdating = False Do While Range("H" & lngRow) < "" Range("N" & lngRow) = "=I" & lngRow & _ "+(HOUR(MAX(J" & lngRow & ":M" & lngRow & "))*2)" lngRow = lngRow + 1 Loop Range("H" & lngSRow & ":N" & lngRow - 1).Sort Key1:=Range("N" & _ lngSRow), Order1:=xlDescending, Orientation:=xlTopToBottom Range("N" & lngSRow & ":N" & lngRow - 1).ClearContents Application.ScreenUpdating = True End Sub If this post helps click Yes --------------- Jacob Skaria "NDBC" wrote: I need to sort the following list of lap times so that all the riders who have come in after the 2:00:00 mark are sorted in terms of number of laps then finishing time. Then the remaining riders who have pulled out before the 2:00:00 mark need to be sorted the same way. Based on the example below the list of rider numbers should end up in the order 405, 411, 401 402 and 409. I have included the cell references as well just in case they are required. I need to do it using vb code so that I can run it from a command buttonl. Thank you for any suggestions. H I J K L M Rider Laps Lap1 lap2 lap3 lap4 No. 5 401 4 0:37:49 1:09:48 1:43:27 1:58:48 6 405 4 0:38:49 1:11:20 1:42:57 2:16:14 7 402 3 0:40:36 1:17:04 1:59:38 8 411 3 0:44:03 1:21:27 2:04:01 9 409 2 0:38:38 1:10:47 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with sorting lists of data.
Disabling Screenupdating will make this routine faster and the user will not
know whats happening...(Especially here we have a temporary column populated with formulas, sort and then clear contents.) If this post helps click Yes --------------- Jacob Skaria "NDBC" wrote: Jacob, Yet again you've given me another great idea. We are not quite there yet though. With your system if there are two riders who both finish on the same lap and go over the 2:00:00 mark they end up with the same rank. The concept is great though and all I need is a bit more thought into fine tuning the weighted combination and I'll be there. What does turning the screen updating do. Does this just make it run faster as it is not constantly changing things as it goes. "Jacob Skaria" wrote: Hi "NDBC" Normal sort will not work to find the rankings..You will have to put weightage for the number of laps as well as the minimum time covered. The below macro which uses Col N as a helper column..Adjust the starting row variable lngSRow to suit your requirement....Try and feedback Sub RankEntries() Dim lngRow As Long, lngSRow As Long lngSRow = 5 lngRow = lngSRow Application.ScreenUpdating = False Do While Range("H" & lngRow) < "" Range("N" & lngRow) = "=I" & lngRow & _ "+(HOUR(MAX(J" & lngRow & ":M" & lngRow & "))*2)" lngRow = lngRow + 1 Loop Range("H" & lngSRow & ":N" & lngRow - 1).Sort Key1:=Range("N" & _ lngSRow), Order1:=xlDescending, Orientation:=xlTopToBottom Range("N" & lngSRow & ":N" & lngRow - 1).ClearContents Application.ScreenUpdating = True End Sub If this post helps click Yes --------------- Jacob Skaria "NDBC" wrote: I need to sort the following list of lap times so that all the riders who have come in after the 2:00:00 mark are sorted in terms of number of laps then finishing time. Then the remaining riders who have pulled out before the 2:00:00 mark need to be sorted the same way. Based on the example below the list of rider numbers should end up in the order 405, 411, 401 402 and 409. I have included the cell references as well just in case they are required. I need to do it using vb code so that I can run it from a command buttonl. Thank you for any suggestions. H I J K L M Rider Laps Lap1 lap2 lap3 lap4 No. 5 401 4 0:37:49 1:09:48 1:43:27 1:58:48 6 405 4 0:38:49 1:11:20 1:42:57 2:16:14 7 402 3 0:40:36 1:17:04 1:59:38 8 411 3 0:44:03 1:21:27 2:04:01 9 409 2 0:38:38 1:10:47 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with sorting lists of data.
Joel,
We did sort it out then but I had forgotten about the need to take out people who do most of the race but then do not finish. I do not understand how your code weeds out the people who do say 4 laps but do not finish like rider 401 for example. "Joel" wrote: I thought we solved this problem at the beginning of July. Try this code. The blanks should sort to the botoom of the table since we are sorting the times descending. If not, I wrote a 2nd macro that puts N/A in the blank cells to force them to the end. This si the same solution you eventually worked out. Sub SortTable1() LastRow = Range("H" & Rows.Count).End(xlUp).Row Col_J = 9 Col_M = 13 For ColCount = Col_J To Col_M Set Key2 = Cells(5, ColCount) Rows("5:" & LastRow).Sort _ key1:=Range("I5"), _ order1:=xlDescending, _ Key2:=Key2, _ order2:=xlAscending Next ColCount End Sub Sub SortTable2() LastRow = Range("H" & Rows.Count).End(xlUp).Row Col_J = 9 Col_M = 13 'fill in empty cells in table For RowCount = 5 To LastRow For ColCount = Col_J To Col_M If Cells(RowCount, ColCount) = "" Then Cells(RowCount, ColCount) = "N/A" End If Next ColCount Next RowCount For ColCount = Col_J To Col_M Set Key2 = Cells(5, ColCount) Rows("5:" & LastRow).Sort _ key1:=Range("I5"), _ order1:=xlDescending, _ Key2:=Key2, _ order2:=xlAscending Next ColCount End Sub "NDBC" wrote: I need to sort the following list of lap times so that all the riders who have come in after the 2:00:00 mark are sorted in terms of number of laps then finishing time. Then the remaining riders who have pulled out before the 2:00:00 mark need to be sorted the same way. Based on the example below the list of rider numbers should end up in the order 405, 411, 401 402 and 409. I have included the cell references as well just in case they are required. I need to do it using vb code so that I can run it from a command buttonl. Thank you for any suggestions. H I J K L M Rider Laps Lap1 lap2 lap3 lap4 No. 5 401 4 0:37:49 1:09:48 1:43:27 1:58:48 6 405 4 0:38:49 1:11:20 1:42:57 2:16:14 7 402 3 0:40:36 1:17:04 1:59:38 8 411 3 0:44:03 1:21:27 2:04:01 9 409 2 0:38:38 1:10:47 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with sorting lists of data.
Jacob,
I have it. The combination is hours*10+no of lap+(60-no of minutes)/100+(60-no of seconds)/10000 so for rider 405 = 24.4446 Then sort by descending order. I trialled my program at an actual dirt bike race on the weekend. 140 riders and it worked perfectly. This was the only drama I had and it was only in the part that determines placings. I owe you a debt of gratitde. Thank you for all of your help and your continued patience with my ineptness when it comes to programming. You will never know just how much this means to our club. THANKS. "Jacob Skaria" wrote: Disabling Screenupdating will make this routine faster and the user will not know whats happening...(Especially here we have a temporary column populated with formulas, sort and then clear contents.) If this post helps click Yes --------------- Jacob Skaria "NDBC" wrote: Jacob, Yet again you've given me another great idea. We are not quite there yet though. With your system if there are two riders who both finish on the same lap and go over the 2:00:00 mark they end up with the same rank. The concept is great though and all I need is a bit more thought into fine tuning the weighted combination and I'll be there. What does turning the screen updating do. Does this just make it run faster as it is not constantly changing things as it goes. "Jacob Skaria" wrote: Hi "NDBC" Normal sort will not work to find the rankings..You will have to put weightage for the number of laps as well as the minimum time covered. The below macro which uses Col N as a helper column..Adjust the starting row variable lngSRow to suit your requirement....Try and feedback Sub RankEntries() Dim lngRow As Long, lngSRow As Long lngSRow = 5 lngRow = lngSRow Application.ScreenUpdating = False Do While Range("H" & lngRow) < "" Range("N" & lngRow) = "=I" & lngRow & _ "+(HOUR(MAX(J" & lngRow & ":M" & lngRow & "))*2)" lngRow = lngRow + 1 Loop Range("H" & lngSRow & ":N" & lngRow - 1).Sort Key1:=Range("N" & _ lngSRow), Order1:=xlDescending, Orientation:=xlTopToBottom Range("N" & lngSRow & ":N" & lngRow - 1).ClearContents Application.ScreenUpdating = True End Sub If this post helps click Yes --------------- Jacob Skaria "NDBC" wrote: I need to sort the following list of lap times so that all the riders who have come in after the 2:00:00 mark are sorted in terms of number of laps then finishing time. Then the remaining riders who have pulled out before the 2:00:00 mark need to be sorted the same way. Based on the example below the list of rider numbers should end up in the order 405, 411, 401 402 and 409. I have included the cell references as well just in case they are required. I need to do it using vb code so that I can run it from a command buttonl. Thank you for any suggestions. H I J K L M Rider Laps Lap1 lap2 lap3 lap4 No. 5 401 4 0:37:49 1:09:48 1:43:27 1:58:48 6 405 4 0:38:49 1:11:20 1:42:57 2:16:14 7 402 3 0:40:36 1:17:04 1:59:38 8 411 3 0:44:03 1:21:27 2:04:01 9 409 2 0:38:38 1:10:47 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with sorting lists of data.
Yes; the formula is the key which I am sure we can improve further..
Cheers and so happy to help you..Take care If this post helps click Yes --------------- Jacob Skaria "NDBC" wrote: Jacob, I have it. The combination is hours*10+no of lap+(60-no of minutes)/100+(60-no of seconds)/10000 so for rider 405 = 24.4446 Then sort by descending order. I trialled my program at an actual dirt bike race on the weekend. 140 riders and it worked perfectly. This was the only drama I had and it was only in the part that determines placings. I owe you a debt of gratitde. Thank you for all of your help and your continued patience with my ineptness when it comes to programming. You will never know just how much this means to our club. THANKS. "Jacob Skaria" wrote: Disabling Screenupdating will make this routine faster and the user will not know whats happening...(Especially here we have a temporary column populated with formulas, sort and then clear contents.) If this post helps click Yes --------------- Jacob Skaria "NDBC" wrote: Jacob, Yet again you've given me another great idea. We are not quite there yet though. With your system if there are two riders who both finish on the same lap and go over the 2:00:00 mark they end up with the same rank. The concept is great though and all I need is a bit more thought into fine tuning the weighted combination and I'll be there. What does turning the screen updating do. Does this just make it run faster as it is not constantly changing things as it goes. "Jacob Skaria" wrote: Hi "NDBC" Normal sort will not work to find the rankings..You will have to put weightage for the number of laps as well as the minimum time covered. The below macro which uses Col N as a helper column..Adjust the starting row variable lngSRow to suit your requirement....Try and feedback Sub RankEntries() Dim lngRow As Long, lngSRow As Long lngSRow = 5 lngRow = lngSRow Application.ScreenUpdating = False Do While Range("H" & lngRow) < "" Range("N" & lngRow) = "=I" & lngRow & _ "+(HOUR(MAX(J" & lngRow & ":M" & lngRow & "))*2)" lngRow = lngRow + 1 Loop Range("H" & lngSRow & ":N" & lngRow - 1).Sort Key1:=Range("N" & _ lngSRow), Order1:=xlDescending, Orientation:=xlTopToBottom Range("N" & lngSRow & ":N" & lngRow - 1).ClearContents Application.ScreenUpdating = True End Sub If this post helps click Yes --------------- Jacob Skaria "NDBC" wrote: I need to sort the following list of lap times so that all the riders who have come in after the 2:00:00 mark are sorted in terms of number of laps then finishing time. Then the remaining riders who have pulled out before the 2:00:00 mark need to be sorted the same way. Based on the example below the list of rider numbers should end up in the order 405, 411, 401 402 and 409. I have included the cell references as well just in case they are required. I need to do it using vb code so that I can run it from a command buttonl. Thank you for any suggestions. H I J K L M Rider Laps Lap1 lap2 lap3 lap4 No. 5 401 4 0:37:49 1:09:48 1:43:27 1:58:48 6 405 4 0:38:49 1:11:20 1:42:57 2:16:14 7 402 3 0:40:36 1:17:04 1:59:38 8 411 3 0:44:03 1:21:27 2:04:01 9 409 2 0:38:38 1:10:47 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with sorting lists of data.
Did the code work
The coe sorts on 2 keys. The first key is the number of laps completed and he second key is the time for each lap. The code sorts each column one at a time starting at J an moving to M. I thought this is the solution you ha in July. Still not sure what was wrong with that code. "NDBC" wrote: Joel, We did sort it out then but I had forgotten about the need to take out people who do most of the race but then do not finish. I do not understand how your code weeds out the people who do say 4 laps but do not finish like rider 401 for example. "Joel" wrote: I thought we solved this problem at the beginning of July. Try this code. The blanks should sort to the botoom of the table since we are sorting the times descending. If not, I wrote a 2nd macro that puts N/A in the blank cells to force them to the end. This si the same solution you eventually worked out. Sub SortTable1() LastRow = Range("H" & Rows.Count).End(xlUp).Row Col_J = 9 Col_M = 13 For ColCount = Col_J To Col_M Set Key2 = Cells(5, ColCount) Rows("5:" & LastRow).Sort _ key1:=Range("I5"), _ order1:=xlDescending, _ Key2:=Key2, _ order2:=xlAscending Next ColCount End Sub Sub SortTable2() LastRow = Range("H" & Rows.Count).End(xlUp).Row Col_J = 9 Col_M = 13 'fill in empty cells in table For RowCount = 5 To LastRow For ColCount = Col_J To Col_M If Cells(RowCount, ColCount) = "" Then Cells(RowCount, ColCount) = "N/A" End If Next ColCount Next RowCount For ColCount = Col_J To Col_M Set Key2 = Cells(5, ColCount) Rows("5:" & LastRow).Sort _ key1:=Range("I5"), _ order1:=xlDescending, _ Key2:=Key2, _ order2:=xlAscending Next ColCount End Sub "NDBC" wrote: I need to sort the following list of lap times so that all the riders who have come in after the 2:00:00 mark are sorted in terms of number of laps then finishing time. Then the remaining riders who have pulled out before the 2:00:00 mark need to be sorted the same way. Based on the example below the list of rider numbers should end up in the order 405, 411, 401 402 and 409. I have included the cell references as well just in case they are required. I need to do it using vb code so that I can run it from a command buttonl. Thank you for any suggestions. H I J K L M Rider Laps Lap1 lap2 lap3 lap4 No. 5 401 4 0:37:49 1:09:48 1:43:27 1:58:48 6 405 4 0:38:49 1:11:20 1:42:57 2:16:14 7 402 3 0:40:36 1:17:04 1:59:38 8 411 3 0:44:03 1:21:27 2:04:01 9 409 2 0:38:38 1:10:47 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with sorting lists of data.
hours*10 will only work fore less than 10 laps completed.
hours*100 will only work for less than 100 laps completed etc. "NDBC" wrote: Jacob, I have it. The combination is hours*10+no of lap+(60-no of minutes)/100+(60-no of seconds)/10000 so for rider 405 = 24.4446 Then sort by descending order. I trialled my program at an actual dirt bike race on the weekend. 140 riders and it worked perfectly. This was the only drama I had and it was only in the part that determines placings. I owe you a debt of gratitde. Thank you for all of your help and your continued patience with my ineptness when it comes to programming. You will never know just how much this means to our club. THANKS. "Jacob Skaria" wrote: Disabling Screenupdating will make this routine faster and the user will not know whats happening...(Especially here we have a temporary column populated with formulas, sort and then clear contents.) If this post helps click Yes --------------- Jacob Skaria "NDBC" wrote: Jacob, Yet again you've given me another great idea. We are not quite there yet though. With your system if there are two riders who both finish on the same lap and go over the 2:00:00 mark they end up with the same rank. The concept is great though and all I need is a bit more thought into fine tuning the weighted combination and I'll be there. What does turning the screen updating do. Does this just make it run faster as it is not constantly changing things as it goes. "Jacob Skaria" wrote: Hi "NDBC" Normal sort will not work to find the rankings..You will have to put weightage for the number of laps as well as the minimum time covered. The below macro which uses Col N as a helper column..Adjust the starting row variable lngSRow to suit your requirement....Try and feedback Sub RankEntries() Dim lngRow As Long, lngSRow As Long lngSRow = 5 lngRow = lngSRow Application.ScreenUpdating = False Do While Range("H" & lngRow) < "" Range("N" & lngRow) = "=I" & lngRow & _ "+(HOUR(MAX(J" & lngRow & ":M" & lngRow & "))*2)" lngRow = lngRow + 1 Loop Range("H" & lngSRow & ":N" & lngRow - 1).Sort Key1:=Range("N" & _ lngSRow), Order1:=xlDescending, Orientation:=xlTopToBottom Range("N" & lngSRow & ":N" & lngRow - 1).ClearContents Application.ScreenUpdating = True End Sub If this post helps click Yes --------------- Jacob Skaria "NDBC" wrote: I need to sort the following list of lap times so that all the riders who have come in after the 2:00:00 mark are sorted in terms of number of laps then finishing time. Then the remaining riders who have pulled out before the 2:00:00 mark need to be sorted the same way. Based on the example below the list of rider numbers should end up in the order 405, 411, 401 402 and 409. I have included the cell references as well just in case they are required. I need to do it using vb code so that I can run it from a command buttonl. Thank you for any suggestions. H I J K L M Rider Laps Lap1 lap2 lap3 lap4 No. 5 401 4 0:37:49 1:09:48 1:43:27 1:58:48 6 405 4 0:38:49 1:11:20 1:42:57 2:16:14 7 402 3 0:40:36 1:17:04 1:59:38 8 411 3 0:44:03 1:21:27 2:04:01 9 409 2 0:38:38 1:10:47 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with sorting lists of data.
Still wasn't quite right. need to use the hours as well. so
if finished then 4000, else 1000+no of lap+(24-no of hours)/100+ (60-no of minutes)/10000+(60-no of seconds)/1000000 "NDBC" wrote: hours*10 will only work fore less than 10 laps completed. hours*100 will only work for less than 100 laps completed etc. "NDBC" wrote: Jacob, I have it. The combination is hours*10+no of lap+(60-no of minutes)/100+(60-no of seconds)/10000 so for rider 405 = 24.4446 Then sort by descending order. I trialled my program at an actual dirt bike race on the weekend. 140 riders and it worked perfectly. This was the only drama I had and it was only in the part that determines placings. I owe you a debt of gratitde. Thank you for all of your help and your continued patience with my ineptness when it comes to programming. You will never know just how much this means to our club. THANKS. "Jacob Skaria" wrote: Disabling Screenupdating will make this routine faster and the user will not know whats happening...(Especially here we have a temporary column populated with formulas, sort and then clear contents.) If this post helps click Yes --------------- Jacob Skaria "NDBC" wrote: Jacob, Yet again you've given me another great idea. We are not quite there yet though. With your system if there are two riders who both finish on the same lap and go over the 2:00:00 mark they end up with the same rank. The concept is great though and all I need is a bit more thought into fine tuning the weighted combination and I'll be there. What does turning the screen updating do. Does this just make it run faster as it is not constantly changing things as it goes. "Jacob Skaria" wrote: Hi "NDBC" Normal sort will not work to find the rankings..You will have to put weightage for the number of laps as well as the minimum time covered. The below macro which uses Col N as a helper column..Adjust the starting row variable lngSRow to suit your requirement....Try and feedback Sub RankEntries() Dim lngRow As Long, lngSRow As Long lngSRow = 5 lngRow = lngSRow Application.ScreenUpdating = False Do While Range("H" & lngRow) < "" Range("N" & lngRow) = "=I" & lngRow & _ "+(HOUR(MAX(J" & lngRow & ":M" & lngRow & "))*2)" lngRow = lngRow + 1 Loop Range("H" & lngSRow & ":N" & lngRow - 1).Sort Key1:=Range("N" & _ lngSRow), Order1:=xlDescending, Orientation:=xlTopToBottom Range("N" & lngSRow & ":N" & lngRow - 1).ClearContents Application.ScreenUpdating = True End Sub If this post helps click Yes --------------- Jacob Skaria "NDBC" wrote: I need to sort the following list of lap times so that all the riders who have come in after the 2:00:00 mark are sorted in terms of number of laps then finishing time. Then the remaining riders who have pulled out before the 2:00:00 mark need to be sorted the same way. Based on the example below the list of rider numbers should end up in the order 405, 411, 401 402 and 409. I have included the cell references as well just in case they are required. I need to do it using vb code so that I can run it from a command buttonl. Thank you for any suggestions. H I J K L M Rider Laps Lap1 lap2 lap3 lap4 No. 5 401 4 0:37:49 1:09:48 1:43:27 1:58:48 6 405 4 0:38:49 1:11:20 1:42:57 2:16:14 7 402 3 0:40:36 1:17:04 1:59:38 8 411 3 0:44:03 1:21:27 2:04:01 9 409 2 0:38:38 1:10:47 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Various lists | Excel Discussion (Misc queries) | |||
sorting lists | Excel Worksheet Functions | |||
Sorting two lists | Excel Discussion (Misc queries) | |||
Sorting lists | Excel Discussion (Misc queries) | |||
Sorting 2 lists | Excel Discussion (Misc queries) |