Advanced filter with calculated criteria (formulas)

[eeigor]

Please provide any information you have on this issue.

Faced a problem when it is necessary to filter records taking into account their length, e.g., CriteriaRange >> SampleFieldName: ="="&(LEN(1stCellRef)>=40). Relative reference to the first cell did not allow processing all records…

There is practically no information on this topic. This issue was not addressed in the Calc guide.
Nothing but an example criteria for dates (BETWEEN Date1 AND Date2). An example file from @Villeroy.
advanced_filter.ods (28.9 KB)

For an example of tasks solved in MS Excel using calculated advanced filter criteria (that is, formulas), I will give a link and a file.

---

Reference Material

Using Advanced Filter in Excel - criteria range examples with formulas

Sample Tasks (MS Excel, XLSX):
FilterAdvCriteriaComplex.xlsx (71.9 KB)
The above file downloaded from here.

Differences:
In MS Excel, in the Сriteria range, the name of the field must be empty or be called something different than in the data set. In LO Calc, the name of the specified field must match the data set.
And if several criteria for one field must be connected using the AND operator, then the column with this field in the Сriteria range must be duplicated as done in the example above (with dates) by @Villeroy.

[erAck]

@eeigor

In MS Excel, in the Сriteria range, the name of the field must be empty or be called something different than in the data set. In LO Calc, the name of the specified field must match the data set.

You are confusing something, filters with on each data point recalculated formulas are not supported in Calc, for other filter criteria the column names must match as in Excel. For a formula in such criteria the result is used as criterion.

[eeigor]

@erAck

You are confusing something, filters with on each data point recalculated formulas are not supported in Calc…

This is what I encountered for the first time. But I just couldn’t know it. You answered the question: not supported. BTW, conditional formatting can do that (iterate over cells).

@erAck

For a formula in such criteria the result is used as criterion.

This limits the range of tasks solved by filtering.
However, the issue is easily resolved by adding an auxiliary column (helper) to the dataset.

[Villeroy]

Excel does not do that neither (judging from the given documentation). I demonstrated how to do the trick in advanced_filter.ods you have linked. Column B calculates the weekday for each row and the adv. filter uses that calculated field. You can do the same with your string lenghts. Within a criteria range one can calculate the criteria (e.g. 5th highest value) but you can not calculate the data to be compared with the criteria.

Needless to say that Base can do this (and a lot more) easier and more reliably.

[eeigor]

@Villeroy

You can do the same with your string lenghts.

That’s right, I wrote about it above (“However, the issue is easily resolved by adding an auxiliary column (helper) to the dataset”).

[Villeroy]

@eeigor

In MS Excel, in the Сriteria range, the name of the field must be empty or be called something different than in the data set.

Did you test this with MS Excel? I can’t believe that.

[eeigor]

Yes. But I preferred to name it something (see: Case-sensitive).


In the picture, there is a problem with relative reference B5: in LO Calc it does not work with a filter, but it works with conditional formatting. And I see some inconsistency in this. But it’s not important…