Google Spreadsheet Post #2167
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. May-23-2017
question by schnikket79:
filter table using multiple keywords that are all located in one column
Here is a sheet I made to help illustrate my problem (it's a reduction of a much larger table).
Here is a sheet I made to help illustrate my problem (it's a reduction of a much larger table).
My large table lists details about various musical works and one of the columns lists the instruments (keywords) that are needed to perform those works.
(In the example sheet, keyword column is column I).
I don't want to change the layout of this table at all (it is used by my other sheets that rely on that particular sort, with all the rows unfiltered).
I want to be able to:
- go into this sheet's Filter View (e.g. Filter1 in my example sheet)
- choose my instruments (i.e. keywords), e.g. type them into the orange cells on Sheet2
- get a filtered table where my Sheet2 choices retrieve matching rows.
Example:
I have there right now Sheet2!A2 == "Piano" and Sheet2!A3 == "Voice"
I want all rows where column I cell contains those two keywords to be shown.
This would include entries such as "Piano, Voice", "Piano, Voice,", or "Piano, Voice, Clarinet in Bb,".
*It would not include entries such as "Piano Solo", for example, because my Sheet2 choice of Piano and Voice requires that both of those keywords be present within one cell.
*To limit my filter to only the rows with Piano, Voice and nothing else, I was thinking of using some kind of "terminator". Say, in Sheet2!A4, I'd put "XXXX".
EDIT: I can imagine "terminator" being a problem. I have a way of working around this issue. So, this doesn't need to be addressed.
I am guessing the above is possible using some clever Filter by Condition custom formula (including some regex stuff), but everything I have tried has failed me so far.)
I would love any help!
No comments:
Post a Comment