Combining SORT(FILTER and IF(ISNUMBER(SEARCH

[ollyhughes1982]

Hi all,




A similar question to yesterday…




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 parkruns with chess pieces within their names (for simplification I have in this example I have restricted to just one piece - ‘Rook’. The parkrun also has to be in the UK. In the workbook, this region’s worksheet is called ‘All Completed Runs - CHECK’.




The following five criteria need to be satisfied:

• The ‘Event (Venue)’ name (Column B) needs to have a name containing ‘Rook’
• The ‘Country’ (Column D) needs to be ‘United Kingdom’
• The Event needs to be an active one - (Column N - ‘Active Event (Venue)?’) needs to be populated with a ‘1’
• The Country needs to be an active one - (Column O - ‘Active Country?’) needs to be populated with a ‘1’
• The Event needs to be a 5K one - (Column P - ‘5K Event (Venue)?’) needs to be populated with a ‘1’

At present I just do a manual filter search of column B and manually copy / paste the list of parkruns, 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 Chess 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.




I t has been suggested that I need an ISNUMBER(SEARCH function for this, but I haven’t been able to get this to work so far.




Thanks in advance!



Olly.

 

[ollyhughes1982]

Yeah, that's fine. I was just thinking for my protected sheets if I wanted to leave some cells unlocked, I didn't want it to appear. it's fine, doesn't really matter. Thanks.

 

[ollyhughes1982]

Sorry, I did have one more thing, re counting in the summary sheet. For the manually inputted East Midlands parkruns, I used to count how many I had done with the following formula:




=IF('All Completed Runs'!B4="","",(COUNTIF('All Completed Runs - EM R (UK)'!B4:B2003,">0"))&" out of"&" "&COUNTIF('All Completed Runs - EM R (UK)'!B4:B2003,"<>"))




What would I now need to change this to? It's actually correct that I haven't done any out of that region, but it should read 0 out of 44, rather than 2000.



Thanks again.

 

[Fluff]

As that's a totally different question, it needs a new thread. Thanks

 

[ollyhughes1982]

OK, no problem