Finding and ranking fastest times over several annual events

Brutality

Brutality

New Member
Joined
Feb 5, 2003
Messages
44
Hi,

It's been a while and I haven't used excel a lot of late so I'm more than a little rusty (although I still code) and now I'm trying to help automate a process for my father.

Each year he holds a car hill climb event, with 4 timed runs per competitor. What I would like to do is analyse all events and produce a report that outputs each drivers single best time (irrespective of car or year). Initially I had each event on a separate sheet, but have amalgamated all data, adding a year column. Data appears as so, up to 2017:

<style type="text/css"> body,div,table,thead,tbody,tfoot,tr,th,td,p { font-family:"Arial"; font-size:x-small } a.comment-indicator:hover + comment { background:#ffd; position:absolute; display:block; border:1px solid black; padding:0.5em; } a.comment-indicator { background:red; display:inline-block; border:1px solid black; width:0.5em; height:0.5em; } comment { display:none; } </style>

<tbody>
[TD="align: center"] YEAR [/TD]
[TD="align: center"] NO. [/TD]
[TD="align: left"] NAME [/TD]
[TD="align: left"] CAR TYPE [/TD]
[TD="align: center"] RUN ONE [/TD]
[TD="align: center"] RUN TWO [/TD]
[TD="align: center"] RUN THREE [/TD]
[TD="align: center"] RUN FOUR [/TD]

[TD="align: center"] 2006 [/TD]
[TD="align: center"] 1 [/TD]
[TD="align: left"] D Shanks [/TD]
[TD="align: left"] Austin Healey [/TD]
[TD="align: center"] 34.87 [/TD]
[TD="align: center"] 34.71 [/TD]
[TD="align: center"] 34.85 [/TD]
[TD="align: center"] 34.73 [/TD]

[TD="align: center"] 2006 [/TD]
[TD="align: center"] 2 [/TD]
[TD="align: left"] E Henderson [/TD]
[TD="align: left"] Jaguar MK2 [/TD]
[TD="align: center"] 34.87 [/TD]
[TD="align: center"] 34.93 [/TD]
[TD="align: center"] 33.20 [/TD]
[TD="align: center"] 32.73 [/TD]

[TD="align: center"] 2006 [/TD]
[TD="align: center"] 3 [/TD]
[TD="align: left"] J McFadzien [/TD]
[TD="align: left"] Singer Vogue [/TD]
[TD="align: center"] 40.73 [/TD]
[TD="align: center"] 40.10 [/TD]
[TD="align: center"] 39.92 [/TD]
[TD="align: center"] 38.95 [/TD]

[TD="align: center"] 2006 [/TD]
[TD="align: center"] 7 [/TD]
[TD="align: left"] P Rissell [/TD]
[TD="align: left"] TR2 [/TD]
[TD="align: center"] 38.95 [/TD]
[TD="align: center"] 38.95 [/TD]
[TD="align: center"] 37.24 [/TD]
[TD="align: center"] 36.98 [/TD]

[TD="align: center"] 2006 [/TD]
[TD="align: center"] 9 [/TD]
[TD="align: left"] B Sheddan [/TD]
[TD="align: left"] Sunbeam Rapier [/TD]
[TD="align: center"] 38.33 [/TD]
[TD="align: center"] 38.33 [/TD]
[TD="align: center"] 37.49 [/TD]
[TD="align: center"] 36.51 [/TD]

[TD="align: center"] 2006 [/TD]
[TD="align: center"] 10 [/TD]
[TD="align: left"] S Quertier [/TD]
[TD="align: left"] V/8 Special [/TD]
[TD="align: center"] 32.52 [/TD]
[TD="align: center"] 32.52 [/TD]
[TD="align: center"] 31.29 [/TD]
[TD="align: center"] 30.06 [/TD]

[TD="align: center"] 2007 [/TD]
[TD="align: center"] 1 [/TD]
[TD="align: left"] D Shanks [/TD]
[TD="align: left"] Austin Healey [/TD]
[TD="align: center"] 34.62 [/TD]
[TD="align: center"] 33.55 [/TD]
[TD="align: center"] 33.27 [/TD]
[TD="align: center"] 33.25 [/TD]

[TD="align: center"] 2007 [/TD]
[TD="align: center"] 2 [/TD]
[TD="align: left"] E Henderson [/TD]
[TD="align: left"] Jaguar MK2 [/TD]
[TD="align: center"] 32.98 [/TD]
[TD="align: center"] 32.58 [/TD]
[TD="align: center"] 32.42 [/TD]
[TD="align: center"] 32.18 [/TD]

[TD="align: center"] 2007 [/TD]
[TD="align: center"] 4 [/TD]
[TD="align: left"] J McFadzien [/TD]
[TD="align: left"] BMW [/TD]
[TD="align: center"] 33.10 [/TD]
[TD="align: center"] 32.58 [/TD]
[TD="align: center"] 31.88 [/TD]
[TD="align: center"] 31.00 [/TD]

[TD="align: center"] 2007 [/TD]
[TD="align: center"] 5 [/TD]
[TD="align: left"] D Harris [/TD]
[TD="align: left"] Johnstone FF [/TD]
[TD="align: center"] 27.05 [/TD]
[TD="align: center"] 26.74 [/TD]
[TD="align: center"] 26.31 [/TD]
[TD="align: center"] 26.46 [/TD]

[TD="align: center"] 2007 [/TD]
[TD="align: center"] 6 [/TD]
[TD="align: left"] N Atley [/TD]
[TD="align: left"] Begg FF [/TD]
[TD="align: center"] 25.97 [/TD]
[TD="align: center"] 25.57 [/TD]
[TD="align: center"] 25.65 [/TD]
[TD="align: center"] 25.46 [/TD]

[TD="align: center"] 2007 [/TD]
[TD="align: center"] 7 [/TD]
[TD="align: left"] B Sheddan [/TD]
[TD="align: left"] Sunbeam Rapier [/TD]
[TD="align: center"] 35.67 [/TD]
[TD="align: center"] 36.04 [/TD]
[TD="align: center"] 37.29 [/TD]
[TD="align: center"] 36.11 [/TD]

[TD="align: center"] 2007 [/TD]
[TD="align: center"] 10 [/TD]
[TD="align: left"] D McDonald [/TD]
[TD="align: left"] Alfa Romeo [/TD]
[TD="align: center"] 38.46 [/TD]
[TD="align: center"] 31.25 [/TD]
[TD="align: center"] 30.88 [/TD]
[TD="align: center"] 31.01 [/TD]

</tbody>


And I want to produce something like the following, sorted by fastest to slowest time for every driver that has ever entered:

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]1[/TD]
[TD]E Henderson[/TD]
[TD]32.18[/TD]
[TD]Jaguar MK2[/TD]
[TD]Run Four[/TD]
[TD]2007[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]D Shanks[/TD]
[TD]33.25[/TD]
[TD]Austin Healey[/TD]
[TD]Run Four[/TD]
[TD]2007[/TD]
[/TR]
</tbody>[/TABLE]



On occasion some entries didn't complete all runs, so there are empty cells here and there. Originally I thought a pivot table might be the solution, but the more I investigate that option the more I think I'm wrong. Any ideas on how to achieve this as I really don't want to do it manually??


All help appreciated and TIA


--
Mark
 
Last edited:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Try this for results on sheet2
Code: 
[COLOR="Navy"]Sub[/COLOR] MG05Oct48
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] oMax [COLOR="Navy"]As[/COLOR] Double, Ray [COLOR="Navy"]As[/COLOR] Variant, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] oMin [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]Dim[/COLOR] Run [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant, K [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = Cells(1).CurrentRegion
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare

[COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
    [COLOR="Navy"]If[/COLOR] Not IsEmpty(Ray(n, 3)) [COLOR="Navy"]Then[/COLOR]
    oMin = 0
    oMax = Application.Max(Application.Index(Ray, n, Array(5, 6, 7, 8)))
    [COLOR="Navy"]For[/COLOR] Ac = 5 To 8
        [COLOR="Navy"]If[/COLOR] Not IsEmpty(Ray(n, Ac)) [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] Ray(n, Ac) < oMax [COLOR="Navy"]Then[/COLOR]
                oMin = Ray(n, Ac)
                oMax = oMin
                Run = Ray(1, Ac)
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Ac
        [COLOR="Navy"]If[/COLOR] Not .Exists(Ray(n, 3)) [COLOR="Navy"]Then[/COLOR]
            .Add Ray(n, 3), Array(Ray(n, 1), Ray(n, 4), Run, oMin)
        [COLOR="Navy"]Else[/COLOR]
            Q = .Item(Ray(n, 3))
                [COLOR="Navy"]If[/COLOR] oMin < Q(3) [COLOR="Navy"]Then[/COLOR]
                    Q(0) = Ray(n, 1): Q(1) = Ray(n, 4): Q(2) = Run: Q(3) = oMin
                [COLOR="Navy"]End[/COLOR] If
            .Item(Ray(n, 3)) = Q
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]

ReDim nray(1 To .Count, 1 To 6)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    c = c + 1
    nray(c, 1) = c
    nray(c, 2) = K
    nray(c, 3) = .Item(K)(3)
    nray(c, 4) = .Item(K)(1)
    nray(c, 5) = .Item(K)(2)
    nray(c, 6) = .Item(K)(0)
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A2").Resize(c, 6)
    .Value = nray
    .Columns.AutoFit
    .Borders.Weight = 2
    .Sort (.Parent.Range("C2"))
    .Parent.Range("A2").Resize(c).Sort (.Parent.Range("A2"))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Mick, that is great, thanks!

Just talked to my Dad and he has decided he wants the fastest times per individual for EACH car they might have run (over the years some entrants have entered different vehicles). Looking at your VB code I'm not sure how to make that happen. Could you help??

Sorry for the change in requirements and thanks again for a speedy answer.
 
Upvote 0
After spending a little time trying to understand the VB code (I was a Perl and PHP developer many years ago) I have what appears to be a working solution, although the output column order isn't what I want (that's next on the list but shouldn't be too difficult). Code only has minor changes:

Code: 
Sub GetFastestCarTimes()
Dim Rng As Range, Dn As Range, n As Long, oMax As Double, Ray As Variant, Ac As Long, oMin As Double
Dim Run As String, Q As Variant, K As Variant, c As Long
Ray = Cells(1).CurrentRegion
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare


For n = 2 To UBound(Ray, 1)
    If Not IsEmpty(Ray(n, 3)) Then
    oMin = 0
    oMax = Application.Max(Application.Index(Ray, n, Array(5, 6, 7, 8)))
    For Ac = 5 To 8
       If Not IsEmpty(Ray(n, Ac)) Then
            If Ray(n, Ac) < oMax Then
                oMin = Ray(n, Ac)
                oMax = oMin
                Run = Ray(1, Ac)
            End If
        End If
        Next Ac
        If Not .Exists(Ray(n, 4)) Then
            .Add Ray(n, 4), Array(Ray(n, 1), Ray(n, 3), Run, oMin)
        Else
            Q = .Item(Ray(n, 4))
                If oMin < Q(3) Then
                    Q(0) = Ray(n, 1): Q(1) = Ray(n, 3): Q(2) = Run: Q(3) = oMin
                End If
            .Item(Ray(n, 4)) = Q
        End If
    End If
Next


ReDim nray(1 To .Count, 1 To 6)
For Each K In .keys
    c = c + 1
    nray(c, 1) = c
    nray(c, 2) = K
    nray(c, 3) = .Item(K)(3)
    nray(c, 4) = .Item(K)(1)
    nray(c, 5) = .Item(K)(2)
    nray(c, 6) = .Item(K)(0)
Next K
With Sheets("Fastest_times_cars").Range("A2").Resize(c, 6)
    .Value = nray
    .Columns.AutoFit
    .Borders.Weight = 2
    .Sort (.Parent.Range("C2"))
    .Parent.Range("A2").Resize(c).Sort (.Parent.Range("A2"))
End With
End With
End Sub
 
Upvote 0
OK I was wrong. Now it is only listing the single fastest time for a specific car model regardless of driver, as opposed to the fastest time per car per driver. Back to the drawing board.
 
Upvote 0
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG06Oct38
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] oMax [COLOR="Navy"]As[/COLOR] Double, Ray [COLOR="Navy"]As[/COLOR] Variant, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] oMin [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]Dim[/COLOR] Run [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant, K [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
Ray = Cells(1).CurrentRegion
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare

[COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
    [COLOR="Navy"]If[/COLOR] Not IsEmpty(Ray(n, 3)) [COLOR="Navy"]Then[/COLOR]
    Txt = Ray(n, 3) & "," & Ray(n, 4)
    oMin = 0
    oMax = Application.Max(Application.Index(Ray, n, Array(5, 6, 7, 8)))
    [COLOR="Navy"]For[/COLOR] Ac = 5 To 8
        [COLOR="Navy"]If[/COLOR] Not IsEmpty(Ray(n, Ac)) [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] Ray(n, Ac) < oMax [COLOR="Navy"]Then[/COLOR]
                oMin = Ray(n, Ac)
                oMax = oMin
                Run = Ray(1, Ac)
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Ac
        [COLOR="Navy"]If[/COLOR] Not .Exists(Txt) [COLOR="Navy"]Then[/COLOR]
            .Add Txt, Array(Ray(n, 1), Ray(n, 4), Run, oMin)
        [COLOR="Navy"]Else[/COLOR]
            Q = .Item(Txt)
                [COLOR="Navy"]If[/COLOR] oMin < Q(3) [COLOR="Navy"]Then[/COLOR]
                    Q(0) = Ray(n, 1): Q(1) = Ray(n, 4): Q(2) = Run: Q(3) = oMin
                [COLOR="Navy"]End[/COLOR] If
            .Item(Txt) = Q
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]

ReDim nray(1 To .Count, 1 To 6)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    c = c + 1
    nray(c, 1) = c
    nray(c, 2) = Split(K, ",")(0)
    nray(c, 3) = .Item(K)(3)
    nray(c, 4) = .Item(K)(1)
    nray(c, 5) = .Item(K)(2)
    nray(c, 6) = .Item(K)(0)
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]With[/COLOR] Sheets("Fastest_times_cars").Range("A2").Resize(c, 6)
    .Value = nray
    .Columns.AutoFit
    .Borders.Weight = 2
    .Sort (.Parent.Range("C2"))
    .Parent.Range("A2").Resize(c).Sort (.Parent.Range("A2"))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Awesome, thanks Mick!

I did manage to get it working "acceptably" with some other added conditionals but it was messy and the layout was still wrong. Your solution is perfect, cheers.
 
Upvote 0
MickG said:
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG06Oct38
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] oMax [COLOR="Navy"]As[/COLOR] Double, Ray [COLOR="Navy"]As[/COLOR] Variant, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] oMin [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]Dim[/COLOR] Run [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant, K [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
Ray = Cells(1).CurrentRegion
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare

[COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
    [COLOR="Navy"]If[/COLOR] Not IsEmpty(Ray(n, 3)) [COLOR="Navy"]Then[/COLOR]
    Txt = Ray(n, 3) & "," & Ray(n, 4)
    oMin = 0
    oMax = Application.Max(Application.Index(Ray, n, Array(5, 6, 7, 8)))
    [COLOR="Navy"]For[/COLOR] Ac = 5 To 8
        [COLOR="Navy"]If[/COLOR] Not IsEmpty(Ray(n, Ac)) [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] Ray(n, Ac) < oMax [COLOR="Navy"]Then[/COLOR]
                oMin = Ray(n, Ac)
                oMax = oMin
                Run = Ray(1, Ac)
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Ac
        [COLOR="Navy"]If[/COLOR] Not .Exists(Txt) [COLOR="Navy"]Then[/COLOR]
            .Add Txt, Array(Ray(n, 1), Ray(n, 4), Run, oMin)
        [COLOR="Navy"]Else[/COLOR]
            Q = .Item(Txt)
                [COLOR="Navy"]If[/COLOR] oMin < Q(3) [COLOR="Navy"]Then[/COLOR]
                    Q(0) = Ray(n, 1): Q(1) = Ray(n, 4): Q(2) = Run: Q(3) = oMin
                [COLOR="Navy"]End[/COLOR] If
            .Item(Txt) = Q
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]

ReDim nray(1 To .Count, 1 To 6)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    c = c + 1
    nray(c, 1) = c
    nray(c, 2) = Split(K, ",")(0)
    nray(c, 3) = .Item(K)(3)
    nray(c, 4) = .Item(K)(1)
    nray(c, 5) = .Item(K)(2)
    nray(c, 6) = .Item(K)(0)
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]With[/COLOR] Sheets("Fastest_times_cars").Range("A2").Resize(c, 6)
    .Value = nray
    .Columns.AutoFit
    .Borders.Weight = 2
    .Sort (.Parent.Range("C2"))
    .Parent.Range("A2").Resize(c).Sort (.Parent.Range("A2"))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With
End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
Click to expand...

Mick, if you are still lurking on these boards (or anyone who can help) I have some new info and a request for help.

So the above code has worked flawlessly for several years worth of data, and it *still* works with a small caveat. This year one of the cars blew the clutch on its second run, so it only had one recorded time. When only one time exists, for whatever reason, it will take the empty value from RUN FOUR (the last data column) as 0.00 and use that as the fastest time (even though the field is empty). As soon as another dummy entry is added all is well. Tested on multiple enries to confirm. This isn't a problem since now that I am aware the issue is easily addressed, but it would be nice to get this one tiny glitch sorted.

TIA.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,901
Messages
6,175,277
Members
452,629
Latest member
SahilPolekar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top