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 column should contain any of these words and then result in a table showing column M, N and AH.
Anybody any idea how to realize this keeping a mind the size of the table?
many thanks,
regards,
3 Replies
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.
=FILTER(CHOOSECOLS(Database,13,14,34),BYROW(Database[Description], LAMBDA(x,SUM(N(ISNUMBER(SEARCH(Criteria[Criteria],x))))=3)))This formula works in my Excel online sample sheet. I've named the large table Database and the small table Criteria.
Thx, in the mean time I came to a similar filter solution however static (declaring criteria 1,2 etc) but yours is dynamic and just a bit better. Many thanks and regards,
