Filter an excel range based on multiple dynamic filter conditions

Question

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:

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

use ISNUMBER(MATCH()) on the TEXTSPLIT array. – Scott Craner Aug 25 '22 at 18:58 — Scott Craner, Aug 25 '22 at 18:58

score 1 · Answer 1 · answered Aug 25 '22 at 19:31

1

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))))

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

answered Aug 25 '22 at 19:31

Spectral Instance

1,971
2
3
6

Your solution works, thanks, but I think @ScottCraner suggestion is simpler one and more generic. According to [FilterXML function description](https://support.microsoft.com/en-us/office/filterxml-function-4df72efc-11ec-4951-86f5-c1374812f5b7), it has some limitations. I created an answer based on his suggestion. Following the steps from your solution I was able to adapt it. – David Leal Aug 25 '22 at 21:16

David Leal · Accepted Answer · 2022-08-25T21:18:23.647

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:

Filter an excel range based on multiple dynamic filter conditions

2 Answers2

Linked