Pls clarify the advanced filter criteria constraints in calc.

[rlaggren]

Calc guide 6.4 says on page 426:
2) … Each individual criterion in the same row is connected with AND, while the criteria groups from each row are connected with OR. Empty cells are ignored. Up to eight criteria rows may be defined for a filter.

I believe I have read elsewhere that rows (contrary to the guide above) are unlimited but only 8 columns are allowed. I hope to make sure of the “real” specs and maybe hear any further info on this kind of criteria definition.

The rest of the post is in case any here need more entertainment.

In practice, I have used 21 columns successfully. Well, mostly. It needs more testing and there appears to be some kind of loop where, every second run, the last column of criteria spec pulls a value from a single set of rows found in the source sheet. This causes two subsets of the result to take turns making it through the filter. Output varies depending on whether a “reset filter” has been performed immediately prior. Always the same two subsets; the other 1000 lines, with 10(?) other subsets - no problem. Another anomaly is the the “reset filter” causes the complete source data to be copied to the output location (the “copy to” option in the AdvancedFilter dialog).

Now, I can understand if nobody here wants to speculate on bad behavior, but there does seem to be some conflicting info about how many rows/columns are acceptable so perhaps that at least can be clarified. And if anybody is privy to calc’s design/features plans, can they enlarge on the future of filter criteria a bit?

Since the filter seems to almost work with larger criteria, it may be easy (frightening words, I know…) to ease the criteria constraints. Seem like this would be very desireable. Like maybe 64 columns (AND) and 1000 rows (OR)? ???

Here is the crime scene for those interested:
link text

The Wk_1 sheet is the target and starts out empty. Regex is needed. The spurious range names are from the actual files I’m working on - they came over with the sheet copy. Use the “Skills…” filter. The data comes from the California state medicare records of medical professionals. The anomaly involves the “City” column where “San Mateo” (all such rows) subset does not appear when the filter is run immediately after a “reset filter” - the PaloAlto subset appears (not what I want). I have not tried experimenting with the “City” criteria. However, the “San Mateo” subset does appear when the filter is run a second time and the “PaloAlto” data does not appear (this is the desired behavior). Delete all data from the target sheet before running filter - the whole source data gets copied to the target when “reset filter” is performed. In my tests this behavior is totally repeatable. Very civilized aberration.

Don’t know if this exact behavior will persist through multiple reboots but it’s survived one thus far.

Regards,
Rufus

[rlaggren]

And I probably can lose most of those NOT criteria - I hope. (I iterate through logic more slowly these days.)

[rlaggren]

14col criteria

( I would prefer uploading to this forum; I use links because I don’t see any formatting options that don’t wrap and thus render the data mostly unreadable. An “attachment” option would be another, perhaps easier, option. Links often die and this seriously degrades the value of info on a venue over time.)

The above criteria snippet with 14 cols tests good when applied against 8000+ rows x 48 cols. It seems the 6.4 Guide quoted above is wrong in stating an AdvancedFilter criteria limit of 8 cols. I don’t find any other meaningful reference to the actual filter constraints in calc. In the absence of documentation anyone working in a production environment cannot use more than 8 cols (cya). However, in more forgiving circumstances, it appears the filter is far more useful would appear from the documentation.

Rufus