Page 1 of 1

[Solved] Multiple filters conflict with each other

Posted: Sun Jan 13, 2019 9:32 am
by SteveBros
I'm trying to find out if this is a bug or something I don't understand. When I apply a filter to a single column it works fine. Then when I display the dropdown on another column I am seeing values that should not be displayed based on the first filter! If I select one of those "rouge" values, calc filters on that and overrides my first filter. I check the first filter and it is still defined but ignored. I would expect multiple filters to work together, "AND"ing all of the filters and therefore continuously narrowing the selection. Any ideas on what is going on here? Thanks!
OpenOffice 4.1.4 OS MAC High Sierra

Re: Multiple filters conflict with each other

Posted: Sun Jan 13, 2019 1:34 pm
by Villeroy
Remove all filters. Make everything visible. If there are still rows hidden, use menu:Format>Rows>Unhide
Select the entire list with all columns and rows, say A1:F1001 for a list with one header row, 1000 data rows and 6 columns.
Apply auto-filter buttons to this list range.

Re: Multiple filters conflict with each other

Posted: Sun Jan 13, 2019 1:35 pm
by Zizi64
Try to apply the filter in one step to all of the columns to be filtered.

Re: Multiple filters conflict with each other

Posted: Sun Jan 13, 2019 7:59 pm
by RusselB
Personally I hate the auto-filter option. That said, based on what you have written, it sounds like the auto-filter is doing an OR rather than an AND.
I'm going to suggest following Villeroy's suggestion, with the exception of the last step, where I'm going to suggest you use the Standard filter option.
Unless you have a lot of filters, you should have little problem with the Standard filter, and that filter gives you the ability to apply multiple filter conditions, which can be ANDed and/or OR'd (both AND and OR conjunctions can be implemented and even intermixed)

Re: Multiple filters conflict with each other

Posted: Sun Jan 13, 2019 9:24 pm
by SteveBros
Thanks everyone for the suggestions! The standard filter seems to work consistently. Choices in subsequent dropdowns don't take previous AND's into consideration, but the results are correct. I like auto-filter because, when working correctly, the dropdowns give you only choices that are valid for the other filters applied. I was able to get it working using Villeroy's suggestion but not consistently. It seems that adding a standard filter, then removing it, might clear up the problem but I can't be sure without hours of testing. I finally decided to create a new file and copy my data into it. So far, the new spreadsheet auto-filters are behaving as expected. Thanks again!