The data is a listing of Formula 1 races (year, race and driver). I want to return the first race listed for each driver as well as the last race.
The attached file shows how the data is arranged. Using "Index" I am able to return the first record for each name but I also need to return the last record as well.
I have done some web searches to no avail. The dataset is over 20000 rows and I wish to have it dynamically update.
Thank you
Jim O
Last edited by JO505; 09-12-2014 at 03:57 PM.
You wont find anything in web searches because this is a Database operation.
Now, you could run a pivot table on the data, and get Max & Min of year for each driver.
Can you swing your table round to go from earliest to latest? That will make things easier, you can then use your INDEX/MATCH to find the 1st match.
Use this for the latest date...
=MAX(IF(Table1[Driver]=$K4,Table1[Year],0))
and this for the venue
=INDEX(Table1[Race],MATCH(MAX(IF(Table1[Driver]=$K4,Table1[Year],0))&K4,Table1[Year]&Table1[Driver],0))
Both lof these are ARRAY formulas, entered using CTRL SHIFT ENTER, not just enter
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
FDibbins,
Thanks for the response and the formulas. They both work perfectly. I do have the data sorted from earliest to latest in my data file I just forgot to sort it in my example.
Again thanks to both for your time.
Jim O
Happy to help
FYI
Iterative calculation was turned on for this workbook and I turned it off which should aid calculation speed.
If you don't want to use the helper column, this will work for you.
Last edited by newdoverman; 09-12-2014 at 05:29 PM.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
I ran into an issue with the formula (see the partial list). It will pick up the last year fine but it returns the first race in that year. I need to match the name for the last race in the year. I am trying to avoid going back through all 24412 rows to enter an exact date if their is another way.
Jim O
How can you tell what the first or last race is as there are no dates attached to the races? In the example, some driver's first race is their last race. With anything more than 1 race, there is nothing to identify first from last or anything in between.
I was afraid of that. I was hopping their was some way to count from the bottom up as the races are in the order ran. I have the dates for each race but they are in another table I guess I will just have to add them to this file and go from there.
Thanks for the response.
Jim O
Actually, if the races are all in the correct sequence, maybe there is (using countifs in a helper). I dont have time right now, but IU will take another look later
If the races are indeed in order, this will find the last race for each driver (as much as I can determine right now) using the order of the drivers in column J or column K (on your worksheet....whichever has the names)
Formula:
Please Login or Register to view this content.
This is assuming the race order is as shown in this example.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks