Sorting by number of laps and completion time. Taking out DNF's.

[NDBC]

I'm having trouble sorting riders times with code. The following code only
partially does the job.

Worksheets("Over 35").Range("G5:IV104").Sort Key1:=Worksheets("Over
35").Range("I5"), Order1:=xlDescending, _
MatchCase:=False, Orientation:=xlSortColumns, DataOption1:=xlSortNormal

MaxLap = Worksheets("Over 35").Range("I5")
Rdone = 0
If MaxLap > 0 Then
For l = MaxLap To 1 Step -1
Onlap = WorksheetFunction.CountIf(Worksheets("Over 35").Range("I5:I104"), l)
If Onlap > 0 Then
tl = 5 + Rdone
br = 4 + Onlap + Rdone
Worksheets("Over 35").Range("G" & tl & ":IV" & br).Sort
Key1:=Worksheets("Over 35").Range("i5").Offset(Rdone, l), _
Order1:=xlAscending, Orientation:=xlSortColumns, DataOption1:=xlSortNormal
Rdone = Rdone + Onlap
End If
Next l
End If

A brief explanation of how it works. Firstly it sorts them by how many laps
each rider has done. Then it sorts the sub groups of riders who have
completed the max number of laps, then the riders who have completed (max-1)
number of laps etc.

Below is the data after I have run the code. The problem is that a rider is
only considered to get a finish if he comes in after the 2 hour mark. This
means that rider 402 did not finish the race and that rider 411 needs to be
ranked above him. Basically I need to do two sorts. One for the riders who
have riden more then 2 hours and one for the riders who did not complete the
race. Can anybody help me with this.


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 2:15: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

Thanks again for your help. I am using Excel 2003.

 

[Dave Peterson]

I'm not sure if I understand, but I think I would add a helper column with a
formula that would indicate whether the rider finished the race.

Maybe something like:
=if(m2>time(2,0,0),"Finished","Not-Finished")

(You could add the formulas to the helper column in code, too.)


Dim LastRow as long
with Worksheets("Over 35")
lastrow = .cells(.rows.count,"H").end(xlup).row
.range("N2",.cells(lastrow,"N")).formula _
= "=if(m2>time(2,0,0),""Finished"",""Not-Finished"")"
end with

And use that as a secondary (or primary???) key with a single sort????