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