I have a spreadsheet with multiple tabs in which I would like to filter all existing rows in one sheet (the Source sheet) based on a set of comma-separated tags.
I have a dropdown at the top of the column in the View sheet in which you can select one or more tags from the set of all comma-separated tags in the Source sheet's. Currently, I am able to easily perform an OR operation by using FILTER with REGEXMATCH, making use of the | Regex operator.
REGEXMATCH('Source'!$N$3:$N, JOIN("|",ARRAYFORMULA(TRIM( SPLIT('View'!$N$3,",") ))) )
As stated, this is an OR operation - adding additional tags to the filter (Cell 'View'!$N$3, a dropdown selector) will expand the results, rather than trim them.
I would like to set this up such that it performs an AND operation, rather than an OR operation. I thought I could try using a positive lookahead, but it doesn't seem like lookaheads are supported by google sheets.
An AND could maybe work across a SPLIT, but using ARRAYFORMULA to surround the REGEXMATCH causes the Source range to flatten as well, which breaks the FILTER.
AND(ARRAYFORMULA(REGEXMATCH('Source'!$N$3:$N, ARRAYFORMULA(TRIM( SPLIT('View'!$N$3,",") )) )))
What would be the suggested method of implementing this? The lines shown are a single filter parameter of a large FILTER method.
Example
For additional context, lets say that 'Source'!$M$3:$M' is a "Name" column containing people's names, and 'Source'!$N$3:$N is a "Hobbies" column, containing comma-separated lists of hobbies. We'll say that John likes Baking, Smith likes Basketball, and Peter likes both Baking, and Basketball (so that 'Source'!N5 equals "Baking, Basketball').
Names | Hobbies |
---|---|
John | Baking |
Smith | Basketball, Ballooning |
Peter | Baking, Basketball, Ballooning |
Edit: Added "Ballooning" to Smith and Peter, since I would like my search to continue to ignore any unrelated tags.
If I set 'View'!$N$3 to "Baking, Basketball", my FILTER on the range 'Source'!$M$3:$M currently outputs three rows, containing "John", "Smith", and "Peter".
Names |
---|
John |
Smith |
Peter |
I would like it to instead output only "Peter". It should also output the same if I instead put "Basketball, Baking", and should not be limited to only two values for 'View'!N3.
Names |
---|
Peter |
I am also retrieving more than 1 column from the dataset - I am only using one for the example to keep it simple.
Different approach, doing the and
outside the regexmatch
on the individual View items by applying it byrow
:
=filter(tocol(Source!M3:M,1),byrow(tocol(Source!N3:N,1),lambda(row,and(regexmatch(row,split(View!N3,", ",1,1))))))
N.B. As per Tom Sharpe's comment to the previous answer, this formula would still give the answer 'Peter' for a View of 'Baking, Basketball' if the Source row contained 'Baking, Basketball, Ballooning'