Hi,
I'm looking for a formula to filter lines of a table depending on a certain criteria. In the image below, for example, I'd like to have a formula that filters the whole line only when the third column is "US". How could I achieve that?
Thanks a lot.
Just to showcase advice from esteemed colleagues @m.a.riosv and @Lupp
This spreadsheet C:\fakepath\Filteres.ods demonstrates four different ways to filter data, they all give the same result, the same as in your picture
In columns F:H, the result is obtained using the formulas.
In columns M:O, the result is obtained using the Advanced Filter based on criteria in cells K1:K2
In columns R:T, the result is obtained using the Standard filter
in columns W:Y - result of UDF (macro)
IMHO, using the built-in filtering mechanisms is much easier than constructing and debugging formulas.
You can achieve that trough Standard filter using their Options
Thanks for you reply! I checked this option, but I'm looking to achieve this with a function (I'm creating multiple spreadsheets automatically and a function would really help me with that). Google Sheets and Excel have a FILTER function that can do that: https://support.google.com/docs/answe... But I'm not sure if LO has something like that. Do you think it's possible? Thanks again.
It isn't extremely difficult to write a user function for the purpose, but what good for? It would need to be a function returning an array, and if used to return its results to a cell range, the range size must be calculated and locked. Any change in a cell used on a parameter position would make that range "dirty", and the next recalculation might result in a different output range (size). Calc is not prepared do change the locked output range on recalc, and I doubt Excel is. How should this be handled?
The already existing array functions, though less afflicted by the issue than a filter function would be, show well that array output comes with annoying problems.
Asked: 2020-08-08 20:58:41 +0200
Seen: 556 times
Last updated: Aug 10 '20
Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.
