How to filter out rows with formula?

[JohnSUN]

@Jalle

It seams as if you solved it?

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)

[Jalle]

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.

[Villeroy]

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.

[eeigor]

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

@Jalle

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.

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.

[eeigor]

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.

[Jalle]

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):

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

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

[eeigor]

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.

@Jalle

Whenever this data occurs in either one of the two colums the whole row should be removed.

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.

[erAck]

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 .

[Jalle]

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.

[Jalle]

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.

[JohnSUN]

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.

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?

@Jalle

And this is offcouse not my native toung.

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?

[Jalle]

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”.

[Jalle]

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

[Jalle]

Dubblepost

[JohnSUN]

@Jalle

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.

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:

[Jalle]

Thanks you so much for that elaborate reply. I will make an effort to absorb everything! It might be upp to 300 rows that builds up gradually . In some dokument there will be less.

Because it’s late here I don’t have time to work on this problem right now however. But would like to through out another question regarding and other problem in this same document that are driving me nuts!

As I mentioned before I’m using this formula in my dokument:

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

Sometimes it generates a negative results. When this happens I would like 0 in that cell instead of the negative number.

I have tried to add many different additional condition and has found two that works on there own namly:

=OM(D20-D21*E21<0;0;D20-D21*E21)
=OM(20-D21*E21<0;D20-D21*E21;0

I have tried the two formulas abow with other preseeding conditions with no problem. But when combined with the first formula I mentiond I simply cant make it work but see no reason why it wouldent.

Edit: I cant se the multiplications in the formulas I refere to. They are soposed på be betwean D21 and E21.

[JohnSUN]

Sorry, I still don’t know which localization uses OM() instead of IF(). Therefore, I can not translate the formula for your Calc. And for the English-speaking Calc, it should be something like:

=MAX(0; IF(B21="RRR";G20+D21*E21;IF(C21="RRR";G20-D21* E21 ;G20 )))

Please note the third parameter of the inner IF() - I have bolded it on purpose.
You are missing it, always. Yes, you are entitled to it. However, if you had indicated it, you most likely would not have encountered this problem:

@Jalle

When othere rows ocures in betwean the RRR rows stuff get messy.

[erAck]

@JohnSUN

which localization uses OM() instead of IF()

sv Swedish. (and MAX() is MAX()).

[Jalle]

Yes its Sweden.

[Jalle]

Brilliant it works! There is only one problem and that’s is when a new negative value is generated below the first one. By this piece of the formula: G20-D21 “Times” E21 (I add Times here because I cant see the multiply sign). In this case the the resulting calculation would look like this: 0-X “Times” X. Hence first time it works and there will be a 0 instead of a negative number. But when RRR occurs in the B column a second time The formula cant deal with it.

Is there away around this? ideally the formula should generate a new 0 in this case as well. And continue doing so until the calculation shifts to G20+D21*E21 and once again generates a positive value.

The full formula looks of course looks like this: =MAX(0; IF(B21=“RRR”;G20+D21*E21;IF(C21=“RRR”;G20-D21"Times"E21)

Also I bin reading about the suggestions you made on filtering (I’m overwhelmed by the helpfulness by the way). Only importing rows with particular values in it is brilliant and will extremely useful in some cases for instance where there are a large data sett.

In some cases however where there is only a smaller sett of data. I think “skipping” rows or rather not including them in calculation would be the best solution. That is because the other rows can be of value in some cases.

Such a solution would have to add an element to this formula;

=MAX(0; IF(B21=“RRR”;G20+D21*E21;IF(C21=“RRR”;G20-D21 “Times” E21

That would enable that the formula skip every row that dosent have RRR in it. This must be fairly complex.

I guess on can say the formula do skip rows already but run in to an obstacles because this G20+D21 “Times” E21 and this G20-D21"Times"E21 piece of the formula will automatically deal with the value in precising row. And this roll will repeat it self when filling up the cells.

In order to work with in an sett och mixed rows where not very row has RRR in A or B. It would require that the formula to take the preceding row with RRR in A or B in to account but don’t include values from intermediate rows in the same column. Lets say the flowing calculation or its negative counterpart is preformed:

G20+D21*E21

And that row 21 is followed by 3 rows that doesn’t have RRR in A or B. Next time this peace of the calculation (if ts even relevant at this point) would have to do this:

G20+D24*E24.

I cant figure out how to realize this. I suppose an other IF value that has RRR and A and B in it would have to be added? Possibly there’s and other operator is this case?

I cant see the Multiply sign i some pieces of the text added this instead “Times”