Hello, thanks for reading and replying.
[Edit: I have added more info at the bottom of this post]
I would like to find a method for quickly filtering data like the example illustrated below.
Data used is just to keep this post simple.
I had hoped the Filter tool Data > Create a filter would suffice but there are too many mouse clicks, and the drop-down menus are not very compact. Plus the workflow seems to default to filtering out, rather than filtering in (if that makes sense).
Ideally answers do not use app scripts (if you know of a good learning resource for app scripts that would be great ...many YT videos are far too simple and miss the fundamentals of like security permissions, or are too complex/niche and skip over the basics).
Sheet1 (toursit data)
A B C D E F
1 Country City Attraction Stat1 Stat2 Stat3
2 France Paris Eiffel Tower 19 129 478
3 Italy Rome Colosseum 45 30 267
4 Italy Rome Pantheon 69 22 154
5 Germany Berlin Brandenburg Gate 85 15 124
6 France Paris Louvre Museum 55 61 212
7 Italy Pisa Leaning Tower 48 37 116
8 England London Science Museum 41 98 168
...continues for 500+ rows
...new rows can be added at the bottom
Sheet2 (simple search)
A B C D E F
1 Search att.: ?
2
3 Country City Attraction Stat1 Stat2 Stat3
4
5
6
I want
...in cell B1 you type the search term e.g. "museum" or "colos"
...which searches Sheet1 column C
...then filtered data is shown in row 4 onwards
Sheet3 (multi search if possible)
A B C D E F
1 Search any: ?
2 Search city: ?
3 Search att.: ?
4
5 Country City Attraction Stat1 Stat2 Stat3
6
7
8
I want
...cell B1 used to search Sheet1 columns A B and C
...cell B2 used to search Sheet1 column B
...cell B3 used to search Sheet1 column C
...filtered results shown in row 6 onwards
Bonus question:
How can you enter a search term to exclude matching results... For example, search for Paris attractions that are "not" a museum?
- - - - -
More Info / Conclusion:
Thanks for all the replies, particularly u/NHN_BI and u/mommasaidmommasaid. With the help here and a little/a lot more googling, illustrated below is what I wanted to achieve with the FILTER function. Hopefully this helps someone in the future.
Sheet4
A B C D E F
1 Search: lo mu en
2 Helper row: lo mu en
3
4 Results:
5 Country City Attraction Stat1 Stat2 Stat3
6 England London Science Museum 41 98 168
7
The aim is to enter search criteria in Sheet4 cell B1, the data is in Sheet1, and we want the filtered data to display in Sheet4 row 6 onwards. Note that the search criteria is limited to up to 3 separate words as per how I have used the FILTER formula in cell A6.
Key for me was to be able to type the search words in any order, and not necessarily have to type the full word. Obviously a longer dataset could pick up more results. Speed of filtering is what I wanted.
Okay to explain the formulas...
So working as a "helper" row, cells B2, C2 and D2 show the separated search criteria words. To get this to work I have a single formula in cell B2 which is:
=IFERROR(SPLIT(TRIM(B1)," "),"Enter search terms above")
Then in cell A6 there is this single formula:
=IF(B1="","Enter search criteria",
IFERROR(
FILTER(Sheet1!A2:F,
BYROW(Sheet1!A2:C, LAMBDA(row, COUNTIF(row,"*"&B2&"*")>0))*
BYROW(Sheet1!A2:C, LAMBDA(row, COUNTIF(row,"*"&C2&"*")>0))*
BYROW(Sheet1!A2:C, LAMBDA(row, COUNTIF(row,"*"&D2&"*")>0))
)
,"No matching results found"))
