Custom Filter with AND and OR conditions

[Skoville]

Hi All,

Absolute beginner here, so please be gentle :)

I am wondering why a custom filter I have built is not returning the expected results - in this example the source is a simple excel file with 282 rows.

I am filtering on two columns within the sheet, "IS VALID" and "CAUSE".

245 Rows in the source file have a value of "VALID", 37 are "INVALID"

The "CAUSE" column is populated on all rows and has 9 distinct values - 01 through to 09

I want the filter to remove all "INVALID" rows and only return rows with a cause equal to 1,2,3 or 4...

122 rows have a cause equal to 1,2,3 or 4, of these rows, 111 have an "IS VALID" value of "VALID" - so in the output of the filter I would expect to see 111 rows - but instead I am seeing all 122 rows - This is the filter I thought should work:

[IS VALID]="VALID"

AND

[CAUSE]="1"

OR

[CAUSE]="2"

OR

[CAUSE]="3"

OR

[CAUSE]="4"

I have worked around it by splitting the two filters - first on "CAUSE" and then a second on the true results of that filter to filter on "IS VALID" - these return the correct results - 111 Valid rows with a Cause of 1,2,3 or 4.

So two questions:
1: Am I misunderstanding the functionality of the "AND" operator?

2: Is there a simpler way to include multiple "OR" values - eg [CAUSE] INLIST "1","2","3","4"]

Appreciate any help you may have,

Cheers,

Sko.

[Skoville]

Ok - Fixed the first issue - forgot the brackets :P

[IS VALID]="VALID"

AND

([CAUSE]="1"

OR

[CAUSE]="2"

OR

[CAUSE]="3"

OR

[CAUSE]="4")

But is there a better way to phrase the "OR" section?

Cheers,

Sko.

[sumitiiest · Accepted solution]

Hi @Skoville ,

You can also phrase your multiple OR conditions in below way.

[IS VALID]="VALID" AND  [CAUSE] IN ("1","2","3","4")

Thanks,

Sumit