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>
</tbody>
And I want to produce something like the following, sorted by fastest to slowest time for every driver that has ever entered:
<tbody>
</tbody>
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
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>
YEAR | NO. | NAME | CAR TYPE | RUN ONE | RUN TWO | RUN THREE | RUN FOUR |
2006 | 1 | D Shanks | Austin Healey | 34.87 | 34.71 | 34.85 | 34.73 |
2006 | 2 | E Henderson | Jaguar MK2 | 34.87 | 34.93 | 33.20 | 32.73 |
2006 | 3 | J McFadzien | Singer Vogue | 40.73 | 40.10 | 39.92 | 38.95 |
2006 | 7 | P Rissell | TR2 | 38.95 | 38.95 | 37.24 | 36.98 |
2006 | 9 | B Sheddan | Sunbeam Rapier | 38.33 | 38.33 | 37.49 | 36.51 |
2006 | 10 | S Quertier | V/8 Special | 32.52 | 32.52 | 31.29 | 30.06 |
2007 | 1 | D Shanks | Austin Healey | 34.62 | 33.55 | 33.27 | 33.25 |
2007 | 2 | E Henderson | Jaguar MK2 | 32.98 | 32.58 | 32.42 | 32.18 |
2007 | 4 | J McFadzien | BMW | 33.10 | 32.58 | 31.88 | 31.00 |
2007 | 5 | D Harris | Johnstone FF | 27.05 | 26.74 | 26.31 | 26.46 |
2007 | 6 | N Atley | Begg FF | 25.97 | 25.57 | 25.65 | 25.46 |
2007 | 7 | B Sheddan | Sunbeam Rapier | 35.67 | 36.04 | 37.29 | 36.11 |
2007 | 10 | D McDonald | Alfa Romeo | 38.46 | 31.25 | 30.88 | 31.01 |
<tbody>
</tbody>
And I want to produce something like the following, sorted by fastest to slowest time for every driver that has ever entered:
1 | E Henderson | 32.18 | Jaguar MK2 | Run Four | 2007 |
2 | D Shanks | 33.25 | Austin Healey | Run Four | 2007 |
<tbody>
</tbody>
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: