IF type formula likely needed

[ollyhughes1982]

Hi all,

Would anyone please be able to help with three calculations in my ‘All Completed Runs - Summary’ worksheet. In cells B117:B119 I am trying to find a way to list my ‘Hoffman’, ‘Bailey’ and ‘Full Bailey’ progress. The definition for this club is:

"XinX" is running at a number of different 5k parkrun venues before running at a venue for a second time - so the parkrunner has run "X" different Events in their first "X" parkruns.

For me, I repeated Newport in my fifth run, so my Hoffman, Bailey and Full Bailey result is 4. i.e. I ran 4 events before ever repeating one. The relevant cells should display as follows:

• B117: '4' / C117 '4 out of 50'
• B118: '4' / C118 '4 out of 100'
• B119: '4' / C119 '4 out of 250'

This can now never change as it is the first time you ever repeat an event, and then that’s it.

I’m guessing this is likely to be a IF formula type solution that’s needed.

Link to file here: Hoffman, Baily, Full Bailey.xlsx

Thanks in advance!

 

[jasonb75]

Enter this formula into CG3 of 'All completed runs' and fill down to the end of your data.

=IFERROR(MATCH(C3,C4:C$2002,0),"")

Then this one into B117 of the summary, array confirmed with Ctrl Shift Enter (may be a different key combination on Mac)

=MIN(IFERROR(INDEX('All Completed Runs'!$CG$3:$CG$2002,MATCH(TRUE,ISNUMBER('All Completed Runs'!$CG$3:$CG$2002),0)),0),50)

Change 50 to 100 and 250 for the other cells.

 

[ollyhughes1982]

Thanks. I’ve tried to avoid array formulas so far, is there a way to do this with the new unique filter options in the latest Excel version? I have this done similarly in a number of tables in my workbook, but couldn’t work out how to make it fit this. Thanks for your help.

 

[jasonb75]

Where would I find the existing unique filter formulas in your workbook?
I'm not using office 365 so don't have the new functions to try anything with, but if I might be able to work something up from an existing formula if I can see how it has been written.

 

[ollyhughes1982]

Where would I find the existing unique filter formulas in your workbook?
I'm not using office 365 so don't have the new functions to try anything with, but if I might be able to work something up from an existing formula if I can see how it has been written.

Thanks. If you look at the 'All Completed Runs - E(V)' worksheet for example. This one filters to unique events completed so far and how many time they have been completed in total. What I am after here is pretty similar to this, but rather than filter to unique events, it should filter to unique events that have been completed at least twice and give a count of them in column B.

 

[jasonb75]

As far as I can see, those functions can only identify the unique records, not their positions within the list.

There might be someone out there who can prove me wrong, but I don't see any way to get what you need without arrays of some kind. Even something on the lines of SMALL(UNIQUE(FILTER(...))) to get the first and second instances of a run would only be able to compare one event without an array, from previous experience such things don't play well when you try to compare multiple items.

There will most likely be other alternative formulas that will work, but ultimately I think that they will all require a helper column in the source sheet and an array formula to summarise.
Beyond that, you would need to look at alternative methods such as vba or power query.

 

[ollyhughes1982]

This is now resolved. Thanks.