How to filter out rows with formula?

As the title suggest I would like to use a formula for filtering out rows that contains a particular value in column A1 or column B2. I don’t want to use any of the filter functions under the menu “data”. But would like to have a formula for this.

I figure this is fairly simple and have made some attempts with functions based on: =FILTER(). But haven’t be able to figure it out. If any one knows how it would be of grate help.

Hi and welcome.

What do you mean by filter?

  1. LO doesn’t have a FILTER function. If you are using Excel, learn about FILTER and spilling from Leila Gharani on YouTube (https://www.youtube.com/c/LeilaGharani)
  2. A formula cannot cause rows to hide, unless you do twisted things by using the UNO API filtering functionality within a User Defined Function. That would be even messier than it sounds.
  3. It is not easy to use standard functions to create picked lists. Usually this requires fairly advanced array functions. However, User Defined Functions (macro functions) can do this pretty easily, but that’s macro programming. That’s probably why Excel has added spilling, etc. If you want to go down that path, start with ExcelJet specifically on the topic of using functions to do these kinds of things. Excel formula: Extract multiple matches into separate columns | Exceljet. This is still done in Excel for various reasons, mostly for backward compatibility with older versions.
  4. Filtering (as you say you do not want to), pivot tables, etc., are the norm for doing these kinds of things.
  5. As some would say…you probably really want a database. Consider learning Base. Once you learn some Base, it is often pretty easy to convert spreadsheets to a database, and the community will definitely bend backward to help you.

What would you like to do with the resulting data set?

Welcome!

Just take my word for it - a formula, even the most complex one, will not do this job.
The macro that will do the job is not very complex. But it’s not clear from your description how many values should be excluded - one specific or a whole list? Should this value take up the entire cell, or can it be part of a text in cell?

Hi! I whas acturly refering to you here:

It seams as if you solved it? In the topic I made a lingvistic eror its not acturly a value its as just a pice of text. And It occurs only separately. And always in one out of two columes.

As I say in my latest post Im not categorically against filters it’s just that I dont think it will work for me based on my attemts. I alos decribe what I whant to achieve in more detail down below.

The solution you refer was solved a different problem - it took the initial data and placed the filtering result in some other place on the sheet. This corresponds to one of the concepts of spreadsheets - do not touch the original data, do not change it, put the results of the calculations in the place where the calculation is performed.
You formulate the task a little differently: I insert data into a predetermined range (in your case, columns A:B) and I want to clean this range on the spot. Yes, this is also a kind of filter, but it differs from the solution given - the key point is “change data in range, in the place”
By the way, how big data arrays do you operate with? Dozens of rows? Hundreds? Thousands? The fact is that there are several different ways to solve this problem - for large amounts of data it is better to use more complex algorithms (otherwise your work will turn into an agonizing wait for results after each action with the original data range)

Yes I past stuff in the dokument that has formulas in it rather then working on the data once its in a dokumet. So far it works perfectly. The dokumet fill upp gradualy with blocks contaning like 30 rows or less. As long as the data is prefilterd and pasted in the right column its no problem. If I could incorperate a filter that removes row as well so I dont have to prefilter till would be grate.

This is what one of my curent formula look like:

=OM(B21="RRR";G20+D21*E21;OM(C21="RRR";G20-D21*E21))

As I mentiond theres is only the RRR rows that are of intresst. When othere rows ocures in betwean the RRR rows stuff get messy.

Also his have to wok in ”real time” so aplying filter retrospectivly in a manual fasion once the rows are fild up are no good (Unless it could be automatic). The equataion is likly to be enougf to get the ideer. But theres some more information about my set up down below.

LibreOffice comes with an SQL database frontend. An SQL SELECT statement is like a “formula” returning a row set from one or more tables, in any order of columns, any order of rows, filtered by arbitrary complex cross-table criteria including calculations.

There are filters to filter data. Use AutoFilter, StandardFilter, AdvancedFilter.

Fairly simple? What you are asking for is too complicated. Give it up. There are plenty of other solutions to your problem that you haven’t described.

Not all LibreOffice users know about Excel’s dynamic arrays, even though they appeared in 2019. You are better off with Excel, which knows how to work with dynamic arrays (FILTER(), SEQUENCE(), SORT(), UNIQUE(), etc., and #SPILL! error*).


* Occurs when there is not enough free space when filling the range with the received data, that is, the range is occupied (and you enter the formula in the upper left corner of the range, the size of which is determined automatically).


You may want to modify this VBA function to suit your needs.

Returns: A zero-based, one-dimensional array of substrings that include or exclude match.

Function WildFilter(SourceArray() As String, ByVal Match As String _
 , Optional ByVal Include As Boolean = True) As String()
 
    Dim astrSelected() As String
    Dim i&, j&
    
    If Include Then
        For i = LBound(SourceArray) To UBound(SourceArray)
            If SourceArray(i) Like Match Then
                ReDim Preserve astrSelected(0 To j)
                astrSelected(j) = SourceArray(i)
                j = j + 1
            End If
        Next
    Else
        For i = LBound(SourceArray) To UBound(SourceArray)
            If Not SourceArray(i) Like Match Then
                ReDim Preserve astrSelected(0 To j)
                astrSelected(j) = SourceArray(i)
                j = j + 1
            End If
        Next
    End If
    
    WildFilter = astrSelected
End Function

I wish I knew what you really want…
The unwillingness on your part to master filters keeps me from advising you to use LO Base (you have to “master” there, too). But others will do it.

Edit:
About how to make life difficult for yourself
Look for Mike Girvin book “CTRL+SHIFT+ENTER. Mastering Excel Array Formulas” (2013).
There were no dynamic array formulas back then. But now this book is relevant to LibreOffice Calc users.
See: “Using an Array Formula for Extracting Data Using SMALL, IF, and INDEX Functions and AND Criteria”. And many other recipes.

Done

Function FilterRange(aData, nSearchColumn%, sMatch$ _
 , Optional bWildcards As Boolean, Optional bInclude As Boolean)
'''	Filter the elements of the original data array, checking them against the sMatch pattern.
'''	Arguments:
'''		aData: Range data array.
'''		nSearchColumn: Number of column to search for.
'''		sMatch: Match pattern.
'''		bWildcards (optional, default is False):
'''			Specifies whether wildcards are enabled in sMatch pattern.
'''		bInclude (optional, default is True):
'''			If True, the filtered rows will be included.
'''	Returns: A zero-based, 1D array of rows that include or exclude match.

	Dim i&, j&, nRowCount&  'row & column indices, filtered row count
	Dim bResult As Boolean  'indicates whether or not the string satisfies the pattern

	If Not IsArray(aData) Then Exit Function
	If IsMissing(bWildcards) Then bWildcards = False
	If IsMissing(bInclude) Then bInclude = True

	' Move rows that meet criterion to beginning of data array.
	nRowCount = 1  'skips header row (column titles)
	For i = LBound(aData) + 1 To UBound(aData)
		If bWildcards Then
			bResult = aData(i, nSearchColumn) Like sMatch
		Else
			bResult = (aData(i, nSearchColumn) = sMatch)
		End If
		If IIf(bInclude, bResult, Not bResult) Then
			nRowCount = nRowCount + 1
			For j = LBound(aData, 2) To UBound(aData, 2)
				aData(nRowCount, j) = aData(i, j)  'copies column values of i-th row to nCount row
			Next j
		End If
	Next i
	' Clear rest of array.
	For i = nRowCount + 1 To UBound(aData)
		For j = LBound(aData, 2) To UBound(aData, 2)
			aData(i, j) = ""
		Next j
	Next i
	FilterRange = aData
End Function

Let’s say Thanks to @JohnSUN.

I have maged put together a document with a lot of formulas. As This stuff dosen come intuativly to me shanging to excell when only this problem remains is a bit to much of mouthfull. Also i I like linux distributions.

I know this is harder in LO then lets say google sheets. But non the less someone actuly do the same thing as like to achieve here (I have seen other discussion such as this one as well):

Altough I cant make sens of this. It seams to be possibel. The condition, aparantly not the solution, in my case is simpel . As my formula sould only deal only with one value that are in one of two colums. Whenever this data acures in ether one of the two colums the whole row sould be remowed.

The ideer her is that the LO sheet sould be abel to fillter out data as they are pasted in the document. All the filters under Data that I have tried semas to work only in retrospect. After the funtions are put in to place they will not filter any new data that comes in to the dokumet. This might be wrong however.

Also Joshua I llike to erase all rows that dosent meet the conditions. Not hide them. There might be a subtel difference here . And yes I mean fillter

A link to FilterRange() written by @JohnSUN with my addition of Like operator.
You enter the array formula yourself in a range equal to the original size.

It looks like we need to add one more parameter (so as not to use negation in the condition):
Optional bInclude As Boolean # default is True
FilterRange(aData, nColumn%, sCriterion$, Optional bUseLikeOperator As Boolean, Optional bInclude As Boolean)

@Lupp wrote (at the link above): “The range size must be calculated and locked”. This is a serious drawback of this solution.

You didn’t state your actual goal, filter out rows to do what?
If it is including/excluding rows for calculation then take a look at the functions that take filter criteria, like COUNTIF(), SUMIF(), …, COUNTIFS(), SUMIFS(), …, and the whole bunch of “database” functions like DCOUNT(), DSUM(), … see Database Functions .

1 Like

I sopse a funtion that simply skips some rows rater than filter out rows would work. Tha requres the capasaty to for instance add or subtact E23 to lets say E12 acoring to my role and simply skipp all the other lines that dosent have RRR in the A or B column. Can that be acheavd by the funcions you refer to?

Heres one of my curent formula

=OM(B21="RRR";G20+D21*E21;OM(C21="RRR";G20-D21*E21))

This might be enougf to get ideer but theres som information down below about how it works.

This is what one of my curent formula that I manged to put to to gether,probobly by sheer luck, look like:

=OM(B21="RRR";G20+D21*E21;OM(C21="RRR";G20-D21* E21))

As I mention abow theres is only the RRR rows that are of interest. When othere rows ocures in between the RRR rows stuff get messy.

This is aditional information that might help to understand what the formula do in each line.

If the doc has a serten value in the A or B column lets say RRR. LO sould run a calculation in a coresponding cell on the same row as that value. Lets say that a calculation would hapen in the E colum. This role repeat it sell diagnolay trough out the dokumet. And valuse in the E colum will be add or subtracted to the preciding cells in the E comum. Depending on whether RRR occurs in the A or B column.

The problem is that the dokument contain plenty of other rows in fact the rows with RRR are in minority. This mess up the entire document once the funtions I made are applied the the rows.

What one can do is to put all the data in an other dokument and use a strandard filter that preseves only the rows where RRR ocurse in A or B. And then past those rows in a documet that can preform the principles described above. This is offcource not optimal. A ”live filter” would be supperior and later alow for some other funtions to work in the same document .

Allso see my reply to to erAck and JohnSun that is allso of general interest.

I hope it’s comprehensible I have very difficult time writing in general. And this is offcouse not my native toung.

Thanks, I’ve been waiting for your reply.
Please next time format the formulas with back quotes (front and back) or just press CTRL+E - otherwise the asterisks turned part of the formula into italics and disappeared.

image

Yes, I already understood - at one time you add about 30 lines of data. And how many completed rows are there in columns A:B? Below the data in these columns there is nothing superfluous, just empty cells?

Yes, I guessed from the OM() function, probably this is the IF() function - usually we turn off the localization of functions, turn on Tools - Options - LibreOffice Calc - Formula - Use English function names to speak the same language with users.
If it’s an IF() function, then I don’t understand the B21="RRR" condition. Are you comparing the entire cell with “RRR” and not part of it?

In order:

  1. Yes sure I think about that

  2. A and B always has a value in them when there are values in the corresponding row. And A and B never have a value in them if there is nothing in the other cells in the corresponding row. The value in A and B also comes with the data I past in the document and are never there in advance. So the document as a whole is always empty below the last line I past in it including the A and B column.

  3. Its an IF function I just dident think. I hope I’m getting you right now. with "B21=“RRR” I dont mean to say that “B21” is the same as “RRR” (Like “A12=A35” or something) it just whant to say that B21 has the value “RRR” in it. And if that’s true the action: “G20+D21E21" should be preformed. The other option is that “RRR” is in A21 and then this action should be preformed: "G20-D21E21”. The cell containing “RRR” dosent have any other value in it. I can assure you that it works. If its a normal syntax i don’t know.

I hope i understood correctly wright the example according to forum policy with “back quotes”.

Hoppfully I got you right here. Im groping in the dark with all I put togheter so far. And might not understan basic qustions. If that the case now I’m happy to try again!

Best regards

Dubblepost

You’re absolutely right, this is one of the easiest and fastest ways to organize data entry into your spreadsheet with on-the-fly data filtering. For example, this code will do exactly this task:

Option Explicit 
Rem To avoid calling a procedure again until the macro has finished running:
Dim bAlreadyInProgress As Boolean 

Const LEAVE_THIS_VALUE = "RRR"	' Keyword that must be in the row

Sub DeleteWithStandardFilterWithTempSheet(oEvent As Variant)
Rem The procedure will copy the source data to a temporary sheet and 
Rem apply a standard filter to it, copying the result to the original location
Dim oSheets As Variant, oSheet As Variant, oTempSheet As Variant, sTempSheet As String 
Dim oSourceRange As Variant, oFilteredRange As Variant, oCursor As Variant
Dim nEndRow As Long
Dim FilterDescriptor As Variant 
Dim oFilterFields2(1) As New com.sun.star.sheet.TableFilterField2
	If bAlreadyInProgress Then Exit Sub
	bAlreadyInProgress = True

	oSheet = oEvent.getSpreadsheet()
	oCursor = oSheet.CreateCursor()
	oCursor.gotoEndOfUsedArea(False)
	nEndRow = oCursor.getRangeAddress().EndRow
	
	oSourceRange = oSheet.getCellRangeByPosition(0, 0, 1, nEndRow)
	If Not IsNull(oEvent.queryIntersection(oSourceRange.getRangeAddress())) Then 
Rem Let's create a temporary auxiliary sheet and copy all the data that should be filtered into it	
		oSheets = ThisComponent.getSheets()
		sTempSheet = "_Temp_" & Format(GetSystemTicks(),"000")
		If oSheets.hasByName(sTempSheet) Then oSheets.removeByName(sTempSheet)
		oSheets.insertNewByName(sTempSheet, oSheets.getCount())
		oTempSheet = oSheets.getByName(sTempSheet)
Rem oSheet.moveRange() should not be used here, otherwise all formulas that refer to these cells will break
		oSheet.copyRange(oTempSheet.getCellByPosition(0, 0).getCellAddress(), oSourceRange.getRangeAddress())
Rem A copy of the data has been created, and the original range can be cleared to make place for the filter result
		oSourceRange.clearContents(7)	
	
		oFilteredRange = oTempSheet.getCellRangeByPosition(0, 0, 1, nEndRow)

Rem The following is a description of the filter. 
Rem In fact, many lines are not needed for this task, they have the desired values by default.
Rem I intentionally list all of them, in case anyone wants to see 
Rem what is affected by changing these properties of the descriptor.	
		FilterDescriptor = oFilteredRange.createFilterDescriptor(True)
		FilterDescriptor.ContainsHeader = True
		FilterDescriptor.CopyOutputData = True
		FilterDescriptor.IsCaseSensitive = False
		FilterDescriptor.Orientation = com.sun.star.table.TableOrientation.COLUMNS
		FilterDescriptor.OutputPosition = oSourceRange.getCellByPosition(0,0).getCellAddress()
		FilterDescriptor.SaveOutputPosition = False
		FilterDescriptor.SkipDuplicates = False
		FilterDescriptor.UseRegularExpressions = False

		oFilterFields2(0).Connection = com.sun.star.sheet.FilterConnection.AND    
		oFilterFields2(0).Field = 0
		oFilterFields2(0).Operator = com.sun.star.sheet.FilterOperator2.EQUAL
		oFilterFields2(0).IsNumeric = False
		oFilterFields2(0).NumericValue = 0.0
		oFilterFields2(0).StringValue = LEAVE_THIS_VALUE
	
		oFilterFields2(1).Connection = com.sun.star.sheet.FilterConnection.OR    
		oFilterFields2(1).Field = 1
		oFilterFields2(1).Operator = com.sun.star.sheet.FilterOperator2.EQUAL
		oFilterFields2(1).IsNumeric = False
		oFilterFields2(1).NumericValue = 0.0
		oFilterFields2(1).StringValue = LEAVE_THIS_VALUE
	
		FilterDescriptor.setFilterFields2(oFilterFields2)

		oFilteredRange.filter(FilterDescriptor)
Rem It remains only to delete the temporary sheet
		oSheets.removeByName(sTempSheet)
	EndIf 
	bAlreadyInProgress = False
End Sub

To be fair, for not very large datasets, the code from the topic you linked to in your question works just as well with a few additions:

Sub DeleteWithLoopInBasic(oEvent As Variant)
Const LAST_SHEET_ROW = 1048575 	' Or use oSheet.getRows().getCount()-1

Dim oSheet As Variant
Dim oCursor As Variant
Dim oQuery As Variant
Dim nEndRow As Long
Dim oCellRange As Variant
Dim oData As Variant
Dim i As Long, k As Long

	If bAlreadyInProgress Then Exit Sub
	bAlreadyInProgress = True
Rem The range for filtering can be significantly smaller than the used area of the sheet. 
Rem We will try to determine its dimensions as accurately as possible.
	oSheet = oEvent.getSpreadsheet()
	oCursor = oSheet.createCursor()
	oCursor.gotoEndOfUsedArea(True)
	nEndRow = oCursor.getRangeAddress().EndRow	' Last used row
Rem Rarely, but it can be: there is something in the very bottom row of the sheet - this can cause an error
	If nEndRow < LAST_SHEET_ROW Then	
		oCellRange = oSheet.getCellRangeByPosition(0, 0, 1, nEndRow+1)
		oQuery = oCellRange.queryEmptyCells()
Rem First empty row after range for filtering:
		nEndRow = oQuery.getByIndex(oQuery.getCount()-1).getRangeAddress().StartRow	
	EndIf 
	oCellRange = oSheet.getCellRangeByPosition(0, 0, 1, nEndRow)
Rem The filtering process is very simple: move the desired rows up	
	If Not IsNull(oEvent.queryIntersection(oCellRange.getRangeAddress())) Then 
		oData = oCellRange.getDataArray()
		k = LBound(oData)
		For i = LBound(oData)+1 To UBound(oData)
			If (UCase(oData(i)(0)) = UCase(LEAVE_THIS_VALUE)) Or (UCase(oData(i)(1)) = UCase(LEAVE_THIS_VALUE)) Then 
Rem ...or If (oData(i)(0) = LEAVE_THIS_VALUE) Or (oData(i)(1) = LEAVE_THIS_VALUE) Then 
				k = k + 1
				oData(k) = oData(i)
			EndIf 
		Next i
Rem The rest of the array needs to be cleared
		If k < UBound(oData) Then 
			ReDim Preserve oData(k)
			oCellRange.clearContents(7)
			oSheet.getCellRangeByPosition(0, 0, 1, k).setDataArray(oData)
		EndIf 
	EndIf 
	bAlreadyInProgress = False
End Sub

You still haven’t answered my question “How many rows are in your table?”. So I can’t really make a recommendation - you should use this algorithm or this one. You will have to make this choice yourself. Do a few experiments with each of the sheets in this spreadsheet FilterInputData.ods (12.7 KB), and decide for yourself.

Pay attention to the line at the very beginning of the code

Const LEAVE_THIS_VALUE = "RRR" ' Keyword that must be in the row

In the process of discussing your problem, you used this placeholder - RRR. That’s why I included it in the macro code. Of course, you will have to replace it with a word that is actually used in your table.

In case you don’t know how to make the macro run at the right moment: just assign the desired code to the sheet event: