I'd like to read the 1st and last results of an individual from their last 10 results from a series of races.
Eg. say I have the past 100 race results, positions from 1 to 5 (first to last); There are many competitors, not all of whom participate in every race. I would like a piece of code that looks at the last 10 races *for each individual* and records how many times they were first and last. First and last can be in two separate cells.
example worksheet attached...
Book1.xlsx
Note that the data set of races is split over different sheets, as in my example.
Are you looking for VBA? If so, I'll move the thread.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
I was hoping to achieve it with regular code. I was thinking COUNTIFS or something similar but I've not been able to achieve it myself and thought better coders might be able to help.
Why not all data in 1 worksheet and use 1 extra column to determine the sheetname (racename)?
Edit:
Having said that, it also will help if you add manualy the expected results in your file.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
My data set is split over many sheets, different sheets for different race regions.
Here is the example sheet with results manually added.
Book1.xlsx
OK - that's not code, it's formulae.
Mmm. I think this is possibly FAR more complicated than you think. Your expected results can't be right, either, can they??? You have only 5 dates in total and (for example) Ringo came 2nd on the 5th May on each sheet. Some sprinter, if eachsheet = different region!!
Oeldere's right. It'd be much easier if everything was compiled onto a single sheet (using formulae, or whatever... first). the single sheet can be hidden, for aesthetic reasons, if you wish.
However, I'm not prepared to spend (potentially a lot of...) time on this until you a) agree the approach and b) provide sample data that are REPRESENTATIVE.
the supply of non-representative dats is a massive timewaster on the Forum....
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Hello Glenn and thanks. The sheet was just an example but I've changed it so the dates are different. I want the formula to report back the 1st and last positions of each sprinter up to his/her last 10 races. Paul for eg has raced 7 times, with a win loss of W 1, L3
It is fine to have all the data on one separate sheet, I could add another data sheet at the end which copies all the entries. I considered this first but then wasn't sure how to best copy the data while avoiding gaps in it. For example if I copy the last 10 races of each region, what if one region only has 2 races? There would then be 8 empty spaces. Whether this is a problem or not I don't know, as I don't know what formula to use to extract the results, COUNTIFS being my best guess.
Book1.xlsx
with a pivot table, after we put all data in the right format in sheet Oeldere.
See the attached file.
Wow. This is amazing. Thank you very much Oeldere.
Now how do I go about getting the data to correctly align on the sheet for the pivot table to read? I know you can't have any gaps with a pivot table.
fill the data in the table (column A - D) like I did.
Then they are ready for analysing with pivot table.
Explain since I don't understandI know you can't have any gaps with a pivot table.
OK. What I meant was, does the table have to be created by hand or can it be created via formula copying the already existing data?
or Can try formula
E5Formula:
Please Login or Register to view this content.
E6Formula:Please Login or Register to view this content.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Thanks for the help, Shukla. But I'm not sure where I should put that code. Could you give more guidelines, or put it into my example workbook so that I can view it there?
You can paste the formula into the cells where requiredI have given cell reference also if you see.
I do but I don't understand.
If I paste your code into a blank cell on the data sheet I just get a ref error. Do I need to place it in a certain cell, in every other cell, or in multiple cells. I'm sure this is what I'm looking for but I just don't know how to implement it.
Here is a copy of your file with shukla's formulas applied.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
I used Power query for it. it is available as add in for excel 2010. It is implemented in Excel 2016.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks