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 @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.
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.
bUseLikeOperator (optional) & my comments added.
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 = aData
End Function
{=FILTERRANGE($A$1:$C$7;3;"US")}
Same result, but more freedom (bUseLikeOperator:=True):
{=FILTERRANGE($A$1:$C$7;3;"U?";1)}
Hi JohnSun. I hope you are reasonably safe, but I realize you might be in the fight to save Ukraine.
If for some reason, you are not, I am a novice user with a silly problem.
I cannot get filterrange to work. It is now a macro in my library.
When I enter {=FILTERRANGE($1:$C$7;3;”US”)} into cell E1, it is of course treated as text
When I enter =FILTERRANGE($1:$C$7;3;”US”) into cell E1, the result is Err:501
Any advice welcome.
I’m fine, thanks for asking.
I’m not sure I understand the meaning of $1:$C$7. Chances are Calc doesn’t understand either. Probably meant $A$1:$C$7?
OK, type to E1 =FILTERRANGE($A$1:$C$7;3;"US"), hold Ctrl key and hold Shift key and press Enter.
This combination of three keys at the same time is usually denoted as Ctrl+Shift+Enter, less often as {CSE}. This is the key combination used to enter Array Functions
Thank you
slava ukraini
Heroyam slava!
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.
It would be useful for formulas…on all matching cells & the values in the same row/col.