Possible IF selection to populate a list

[ollyhughes1982]

Hi all,



I have a workbook in which I keep records of all of my parkruns and associated challenges etc. (Some of you may be familiar with my previous queries whilst developing this workbook). One such type of challenge is completing ’Regions’ all over the world (in this example, ‘East Midlands’ in the UK. In the workbook, this region’s worksheet is called ‘All Completed Runs - EM R (UK)’.



The challenge requires completing all 43 parkruns within the UK region of ‘East Midlands’ and which are a currently an active event. i.e. Within the ‘parkrun Reader Dump’ worksheet (which contains all of the parkrun source data), the following three criteria need to be satisfied:

• The ‘Country’ (Column D) needs to be ‘United Kingdom’
• The ‘Region’ (Column L) needs to be ‘East Midlands’
• The Event needs to be an active one - (Column N - ‘Active Event (Venue)?’) needs to be populated with a ‘1’

At present I just copy / paste the list of parkruns for the region, from the relevant area within the ‘parkrun Reader Dump’ worksheet. So, I just want to automate what I currently do via a manual copy / paste process - which can become quite time consuming, as I have many of these regions in the workbook.



Additionally, I would like them listed alphabetically, as they currently are, in the manually populated version.



I believe the date column should work as it does at present - i.e. Display the first date that I did that particular event, otherwise appear blank.



I’ve highlighted what I think are relevant worksheets in yellow



Link to workbook (One Drive): parkrun Regions Example.xlsx



Apologies the workbook’s large and may take up to a minute to open. - I can’t cut it down in size for this example, as there are so many interlinked worksheets etc. that are required for it to work.



Thanks in advance!



Olly.

 

[Fluff]

Maybe something like

=SORT(FILTER('parkrun Reader Dump'!B3:B500,('parkrun Reader Dump'!D3:D500="United Kingdom")*('parkrun Reader Dump'!L3:L500="east midlands")*('parkrun Reader Dump'!N3:N500=1)))

 

[ollyhughes1982]

Yes, this worked great - I just extended the range down to 5003 to include all possible rows and I then get the 44 correct active parkruns for that country and region. I assume that if I want to add in any extra criteria, I can simply just keep adding further *(... onto the formula?

Also, I have some other challenges where I want to search the names for particular sequences of characters. Can I use something for contains rather than equals? For example, there is a chess challenge, so would want to search the parkrun names which contain 'rook', 'knight', 'queen' etc. anywhere within the name.

Thanks for your help.

 

[Fluff]

I assume that if I want to add in any extra criteria, I can simply just keep adding further *(... onto the formula?

That's right.

For the other question you can use isnumber(search

 

[ollyhughes1982]

That's right.

For the other question you can use isnumber(search

Thank you. I will give this a try for all of my various ones.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[ollyhughes1982]

Hi. Do you think you might be able to show an example of how that chess pieces example might work. Struggling with the ISNUMBER formula.

For example, if the name contains 'Rook' or 'Bishop' and the parkrun is in the UK, and is active.

Thanks again.

 

[ollyhughes1982]

I have tried this formula (searching for 'Boston parkrun' - as it is in the East Midlands list), but get a value error.

=SORT(FILTER('parkrun Reader Dump'!B3:B5003,('parkrun Reader Dump'!N3:N5003=1)*('parkrun Reader Dump'!O3:O5003=1)*('parkrun Reader Dump'!P3:P5003=1)*('parkrun Reader Dump'!D3:D5003="United Kingdom")*IF(ISNUMBER(SEARCH("Boston parkrun",'parkrun Reader Dump'!B3:B5003)),'parkrun Reader Dump'!B3:B5003,"")))

 

[Fluff]

Is "Boston parkrun" the full name of the event?

 

[ollyhughes1982]

Is "Boston parkrun" the full name of the event?

It is, yes. If I use the IF(ISNUMBER formula on its own, it does find it, but it is way down the page.