Combining SORT(FILTER and IF(ISNUMBER(SEARCH

ollyhughes1982

Active Member
Joined
Nov 27, 2018
Messages
470
Office Version
  1. 365
Platform
  1. MacOS
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.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,214
Office Version
  1. 365
Platform
  1. Windows
It should be like
Excel Formula:
=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")*(ISNUMBER(SEARCH("rook",'parkrun Reader Dump'!B3:B5003)))))
 

ollyhughes1982

Active Member
Joined
Nov 27, 2018
Messages
470
Office Version
  1. 365
Platform
  1. MacOS
It should be like
Excel Formula:
=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")*(ISNUMBER(SEARCH("rook",'parkrun Reader Dump'!B3:B5003)))))
Great
It should be like
Excel Formula:
=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")*(ISNUMBER(SEARCH("rook",'parkrun Reader Dump'!B3:B5003)))))
Great. That worked perfectly for the one, thanks. One more thing, how can I add in the other words, i.e. Pawn, Knight, Bishop, Queen, King, please? I have tried Queen as a test, as I know there are three of these in the UK, but it seems to ignore this and still only includes 'Rook' ones. This is the formula I tried:
Great. That worked perfectly for the one, thanks. One more thing, how can I add in the other words, i.e. Pawn, Knight, Bishop, Queen, King, please? I have tried Queen as a test, as I know there are three of these in the UK, but it seems to ignore this and still only includes 'Rook' ones. This is the formula I tried: =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")*(ISNUMBER(SEARCH("Rook",'parkrun Reader Dump'!B3:B5003)*(ISNUMBER(SEARCH("Queen",'parkrun Reader Dump'!B3:B5003))))))). Do I need some sort of AND function in there?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,214
Office Version
  1. 365
Platform
  1. Windows
Please do not put your reply inside quotes, it looks as though you have just posted a formula & nothing else.
To do that you need to use or on the last parts like
Excel Formula:
=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")*((ISNUMBER(SEARCH("Rook",'parkrun Reader Dump'!B3:B5003)+(ISNUMBER(SEARCH("Queen",'parkrun Reader Dump'!B3:B5003))))))))
 

ollyhughes1982

Active Member
Joined
Nov 27, 2018
Messages
470
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

Please do not put your reply inside quotes, it looks as though you have just posted a formula & nothing else.
To do that you need to use or on the last parts like
Excel Formula:
=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")*((ISNUMBER(SEARCH("Rook",'parkrun Reader Dump'!B3:B5003)+(ISNUMBER(SEARCH("Queen",'parkrun Reader Dump'!B3:B5003))))))))
Thanks, that one didn't seem to work on mine though - I'm still just getting the Rook ones. I have attached a screenshot. The three additional UK Queen ones should be Queen Elizabeth parkrun, Queen's parkrun, Belfast and Queen's parkrun, Glasgow.
Screenshot 2021-10-14 at 13.25.34.jpg
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,214
Office Version
  1. 365
Platform
  1. Windows
I got some of the brackets wrong, try
Excel Formula:
=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")*((ISNUMBER(SEARCH("Rook",'parkrun Reader Dump'!B3:B5003)))+(ISNUMBER(SEARCH("Queen",'parkrun Reader Dump'!B3:B5003))))))
 
Solution

ollyhughes1982

Active Member
Joined
Nov 27, 2018
Messages
470
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

Brilliant, that works perfectly. Thanks again for all of your help
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,214
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

ollyhughes1982

Active Member
Joined
Nov 27, 2018
Messages
470
Office Version
  1. 365
Platform
  1. MacOS
One last silly question - it's a very minor thing. With these FILTER functions, is there a way in Excel to not show the very light blue border around relevant cells, which appears wit this function? It's only an aesthetic thing, and not that important. Just wondered if there was a setting to switch this off?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,214
Office Version
  1. 365
Platform
  1. Windows
Just don't select any of the cells within the spill range.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,044
Messages
5,835,103
Members
430,342
Latest member
sdelan

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
Top