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

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.

3 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.
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)}

1 Like

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!

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.

1 Like

It would be useful for formulas…on all matching cells & the values in the same row/col.