Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting by number of laps and completion time. Taking out DNF's.
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting by number of laps and completion time. Taking out DNF's.
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(m2time(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(m2time(2,0,0),""Finished"",""Not-Finished"")" end with And use that as a secondary (or primary???) key with a single sort???? NDBC wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Processing taking a really long time | Excel Discussion (Misc queries) | |||
Calculation completion date/time | Excel Worksheet Functions | |||
Data Sorting Is Taking Columns That I Don't Want To Be Sorted | Excel Discussion (Misc queries) | |||
Sorting a Column and taking row information with it. | Excel Worksheet Functions | |||
how do i convert a number into minutes for a time of completion c. | Excel Discussion (Misc queries) |