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