CODEWITHSUNDEEP
excelexcel-formula
David Leal
David Leal

Reputation: 6749

Filter an excel range based on multiple dynamic filter conditions

I would like to filter a dataset considering several filter condition that can change dynamically based on a list of possible values. I have filter criteria team which have the following values: Team A, Team B, ALL, where ALL is a wildcard representing all teams. For the second criteria release, in case I want to include more than one release in the filter, the values are separated by comma, for example: Release: A, B, A,B, where the last one means to include both release A and B. I was able to make it works except for the case when the release value is A,B.

I think I should use: TRANSPOSE(TEXTSPLIT(B4,", ")) to obtain in a column the list of values and then in some how (maybe using MMULT) to be able to add the filter condition per each row, but I was not able to do it (in Excel, because using Google Spreadsheet I was able to do it using a query function)

The following formula for single release value works for both criteria:

FILTER(D3:H8, (IF(B3="ALL", D3:D8<>"*",D3:D8=B3)) * (E3:E8=B4))

but it doesn't work for the general case where release values are more than one.

I am looking for a solution that would not require a VBA script

Here is the sample file: Dynamic Filter Sample

Here is a link to the sample excel file: https://1drv.ms/x/s!AlZxw2GG3C7IhyhTnBFbln1G8fOj?e=C8OUxn

Upvotes: 0

Views: 1228

Answers (2)

David Leal
David Leal

Reputation: 6749

Expanding the idea suggested by: @scottCraner, this would be the solution for the release condition:

ISNUMBER(MATCH(E3:E9,TRANSPOSE(TEXTSPLIT(B5,", ")),0))

so the entire formula will look like:

=FILTER(D3:H9, (IF(B3="ALL", D3:D9<>"*",D3:D9=B3)) * 
   (ISNUMBER(MATCH(E3:E9,TRANSPOSE(TEXTSPLIT(B4,", ")),0))))

Note: it works also removing the TRANSPOSE function, i.e.:

=FILTER(D3:H9, (IF(B3="ALL", D3:D9<>"*",D3:D9=B3)) * 
   (ISNUMBER(MATCH(E3:E9,TEXTSPLIT(B4,", "),0))))

Here is the sample solution: enter image description here

Upvotes: 0

Spectral Instance
Spectral Instance

Reputation: 2494

This appears to work:

=FILTER(D3:H9, (IF(B3="ALL", D3:D9<>"*",D3:D9=B3)) * (ISNUMBER(XMATCH(E3:E9,FILTERXML("<c><e>"&SUBSTITUTE(B4,", ","</e><e>")&"</e></c>","//e"),0))))

screenshot illustrating propo (if you decide to have multiple releases delimited by a comma without the space then the SUBSTITUTE() function will require to be updated accordingly)

Upvotes: 1

Related Questions