Help with sorting lists of data.

[NDBC]

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

 

[Jacob Skaria]

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

 

[Joel]

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]

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]

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

 

[NDBC]

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.

 

[NDBC]

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]

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

 

[Joel]

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]

hours*10 will only work fore less than 10 laps completed.

hours*100 will only work for less than 100 laps completed etc.

 

[NDBC]

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