Finding consecutive occurrences of countries

[ollyhughes1982]

Hi. I’m trying to populate the yellow cells in my ‘All Completed Runs - J C’, ‘All Completed Runs - L C’, ‘All Completed Runs - TH C’ and ‘All Completed Runs - BR C’ worksheets.

What I need to display:

• In ‘All Completed Runs - J C’: all 7 events that have made up a run of 7 consecutive events which have been at 7 different countries.
• In ‘All Completed Runs - L C’: all 8 events that have made up a run of 8 consecutive events which have been at 8 different countries.
• In ‘All Completed Runs - TH C’: all 12 events that have made up a run of 12 consecutive events which have been at 12 different countries.
• In ‘All Completed Runs - BR C’: all 15 events that have made up a run of 15 consecutive events which have been at 15 different countries.

I am already doing a similar kind of thing in the ‘Consecutive Countries example’ worksheet, but this looks for the longest run of consecutive events in all different countries, no matter how many. So, if you have done 2, it will list 2, 3 it will list 3 and so on. Here, I want it to only list all events in the longest run, if they meet the amount required for each worksheet. Otherwise, I want it to show the latest unended run, which could be just the last event completed (if it is the latest start of a new run).

The case here, then, is that ‘All Completed Runs - J C’ and ‘All Completed Runs - L C’, should both be fully completed (7 & 8 country runs), as my longest run is 9 consecutive different countries (August - September, 2019). This started with ‘Ross-on-Wye parkrun’ and ended with ’parkrun Kolomenskoe’. A United Kingdom parkrun was then again completed and ended the run. Whereas ‘All Completed Runs - TH C’ and ‘All Completed Runs - BR C’ should just show my latest unended run - my latest 3 runs in this case. As I haven’t yet made a run of 12, which is what would be required for ‘All Completed Runs - TH C’ or 15 for the ‘All Completed Runs - TH C’ worksheet to be completed.

Hope this makes sense. I have manually entered what should be displayed in the yellow cells.

I have attached a link to my file, here: parkrun - My parkrun Record - CONSECUTIVE DIFFERENT COUNTRIES.xlsx

Thanks in advance!

Also posted on Excel Forum: Finding consecutive occurrences of countries

 

[ollyhughes1982]

Hi. Still at a loss on this one, if anyone might be able to help please? Thanks.

 

[ollyhughes1982]

Hi. Still haven’t been able to resolve this. Are there any websites / services where you can pay a small one-off fee and someone can solve an Excel problem? I just can’t figure this one out. Thanks.

 

[Fluff]

Click on the MrExcel Publishing button at the top of the board & then select Excel Consulting Services.
You will find some consultants there.

 

[ollyhughes1982]

Great. Thanks.

 

[ollyhughes1982]

I’m pretty much there with it. I have the function to calculate the streak, I just don’t know how to make it revert to the latest unbroken streak when it doesn’t meet the minimum streak for that worksheet. Thanks

 

[ollyhughes1982]

This might explain a bit better? I have met the criteria to complete the ‘All Completed Runs - J C’ worksheet (7 consecutive countries required) and the ‘All Completed Runs - L C’ (8 consecutive countries required), as I have a streak of 9 consecutive countries in rows 196-204. So both of these worksheets should be completed. However, I have not completed enough for the ‘All Completed Runs - TH C’ and ‘All Completed Runs - BR C’ (12 and 15 events required, respectively). These two worksheets should therefore just show the latest unbroken streak (rows 226-228). I hope this might explain it a bit better. I know it's a tricky one. Thanks.

 

[ollyhughes1982]

Aware that I haven't had any responses / suggestions / solutions proposed For this query. This is likely my fault for not explaining clearly. Is there any way I could explain illustrate it better? I have tried to make it as least complex as I can. Like I have said before, I think I have the main crux of it, in that I have a formula that calculates the streaks. I just don’t know how to get it to revert to the latest unbroken streak, when the minimum amount is not satisfied. Thanks again, all.

 

[ollyhughes1982]

I can't find a solution to this one. So will move on.