Forum Discussion
Dynamically filter table on basis of range of criteria
Hi, I have a large table (150k rows) loaded to Excel from power query and I want to filter the description column (column N) dynamically on a couple of key words. It should be an AND filter so the...
Not 100% sure what type of criteria you wanted because "it should be an AND filter so the column should contain any of these words" is somewhat contradictory. "Any of these words" suggests OR criteria, whereas "all of these words" would mean AND criteria.
If you have the new REGEXTEST function, you could try the following using OR criteria:
=CHOOSECOLS(FILTER(Table3[[SuppliersTradeItemId]:[GrossPrice]],REGEXTEST(Table3[Description],TEXTJOIN("|",,Table4[Criteria]))),1,2,-1)Also, using AND criteria:
=CHOOSECOLS(FILTER(Table3[[SuppliersTradeItemId]:[GrossPrice]],BYROW(REGEXTEST(Table3[Description],TOROW(Table4[Criteria],1)),AND)),1,2,-1)There's a good video on YouTube, Excel Filter Function With RegexTest Function, which includes various other examples; however, no explanation was offered for using AND criteria... the formula shown above was the best I could figure it, utilizing BYROW-AND over an array of patterns. If someone else knows of a better way, I'd love to hear it.