Ask Your Question

Function/formula to filter

asked 2020-08-08 20:58:41 +0200

Luis17 gravatar image


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.

image description

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2020-08-10 12:02:42 +0200

JohnSUN gravatar image

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.

edit flag offensive delete link more


Wow, thanks so much for that. The formula in column F was exactly what I was looking for and I also learned how it can be done in different ways. Really appreciate your help!

Luis17 gravatar imageLuis17 ( 2020-08-10 20:34:31 +0200 )edit

answered 2020-08-09 13:41:44 +0200

m.a.riosv gravatar image

You can achieve that trough Standard filter using their Options

edit flag offensive delete link more


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: But I'm not sure if LO has something like that. Do you think it's possible? Thanks again.

Luis17 gravatar imageLuis17 ( 2020-08-09 13:58:01 +0200 )edit

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.

Lupp gravatar imageLupp ( 2020-08-09 16:36:45 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-08-08 20:58:41 +0200

Seen: 556 times

Last updated: Aug 10 '20