Run at parkrun events different numbers of consecutive times

ollyhughes1982

Well-known Member
Joined
Nov 27, 2018
Messages
760
Office Version
  1. 365
Platform
  1. MacOS
Hi,

I have a list of different parkrun events that I have completed and the number of times that I have completed each one (image attached).

Screenshot 2022-08-14 at 18.45.31.png


There is a challenge in the parkrun community where you calculate your “Rutter Triangle” (not sure where the name comes from!). i.e. Run at parkrun events different numbers of times and then find the longest consecutive run from 1 upwards. For example, in my list I have lots of 1s, so that’s 1; then two 2s at Pontypridd and Severn Bridge, so that’s 2; then three 3s at Aberbeeg, Penallta and Coed Cefn-pwll-du, so that’s 3 - Therefore my Rutter Triangle number is 3. (1, 2 and 3)

As an example, to increase my number to 4 I would need to run either Aberbeeg, Penallta or Coed Cefn-pwll-du once more, to move that event’s total completed up to 4. So I would then have 1, 2, 3 and 4 (Rutter Triangle number of 4).

Additionally, to increase my number to 6 I would need to run either Aberbeeg, Penallta or Coed Cefn-pwll-du twice more and either Aberbeeg, Penallta or Coed Cefn-pwll-du once more to move those event’s totals completed up to 4 and 5. So I would then have 1, 2, 3, 4, 5 and 6 (Rutter Triangle number of 6). N.B. I already have the 6, so this would bridge the gap of 4 and 5.

Hope this makes sense!

Thanks in advance!

Olly.
 
Looking at your workbook, the problem you have is that there is absolutely no link between All Completed Runs, and your All Completed Rutter Runs. If I imagine your Rutter sheet is blank, and you want to populate the event name and date, just by looking at a number 1, or Number2 in col. A, theres nothing that exists with a "2 or above" on your all runs sheet. So its not quite as straightforward as you'd like.

There might be something possible, but it requires a whole new train of thought.. and time. So best not to hold your breath for too long .. Maybe someone else can also have a think about it here for you..
Rob
No problem, thanks. I will try and explain a bit better below and see if anyone comes up with anything.

Re the below two images, I am trying to come up with a formula in column B4:B2003 of the ‘All Completed Runs - Rutter T’ worksheet, to find when an event (venue) has been completed the number of times stated in column A4:A2003. In the images (and linked file), I have populated these manually (in yellow), to illustrate what I am trying to achieve.

Part 1.jpg


Part 2.jpg


If multiple events have been completed the same number of times I would like to show the event name and date completed of the most recent one completed that number of times.

The ‘All Completed Runs’ worksheet is the original source data.

Link to file (small file and will open quickly) is here: Rutter Triangle.xlsx
 
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
How about
Excel Formula:
=LET(f,SORT(FILTER('All Completed Runs'!C4:E2003,'All Completed Runs'!C4:C2003<>""),3,-1),u,UNIQUE(INDEX(f,,1)),c,COUNTIFS('All Completed Runs'!C4:C2003,u),b,BYROW(SEQUENCE(2000),LAMBDA(br,INDEX(FILTER(u,c=br,""),1))),CHOOSE({1,2},b,BYROW(b,LAMBDA(br,INDEX(FILTER(f,INDEX(f,,1)=br,{"","",""}),1,3)))))
 
Upvote 0
Solution
How about
Excel Formula:
=LET(f,SORT(FILTER('All Completed Runs'!C4:E2003,'All Completed Runs'!C4:C2003<>""),3,-1),u,UNIQUE(INDEX(f,,1)),c,COUNTIFS('All Completed Runs'!C4:C2003,u),b,BYROW(SEQUENCE(2000),LAMBDA(br,INDEX(FILTER(u,c=br,""),1))),CHOOSE({1,2},b,BYROW(b,LAMBDA(br,INDEX(FILTER(f,INDEX(f,,1)=br,{"","",""}),1,3)))))
That is amazing and works perfectly. I can't even begin to understand how that is working! Thanks.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

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