# Function/formula to filter

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.

image description

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: FILTER function - Docs Editors Help
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.

Just to showcase advice from esteemed colleagues @mariosv and @Lupp

This spreadsheet 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.

2 Likes

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!

@JohnSUN, filterRange() is simple & good.

``````Function FilterRange(aData, nColumn%, sCriterion\$, Optional bUseLikeOperator As Boolean)
Dim i&, j&, nCount&  'row & column indices, filtered row count
Dim bOK As Boolean

If IsMissing(bUseLikeOperator) Then bUseLikeOperator = False

' Move rows that meet criterion to beginning of array.
nCount = 1  'skips header row (colum titles)
For i = LBound(aData) + 1 To UBound(aData)
If bUseLikeOperator Then
bOK = aData(i, nColumn) Like sCriterion
Else
bOK = (aData(i, nColumn) = sCriterion)
End If
If bOK Then
nCount = nCount + 1
For j = LBound(aData, 2) To UBound(aData, 2)
aData(nCount, j) = aData(i, j)  'copies column values of this row
Next j
End If
Next i
' Clear rest of array.
For i = nCount + 1 To UBound(aData)
For j = LBound(aData, 2) To UBound(aData, 2)
aData(i, j) = ""
Next j
Next i
`{=FILTERRANGE(\$A\$1:\$C\$7;3;"US")}`
`{=FILTERRANGE(\$A\$1:\$C\$7;3;"U?";1)}`