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:
1 Like
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.
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:
sv Swedish. (and MAX() is MAX()).
1 Like
Yes its Sweden.
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”
I was wrong this one actually skips rows progressively when rows are negative. It also skip’s rows that dosent have RRR in B or C!
=MAX(0;OM(B2=“RRR”;G1+D2E2;OM(C2=“RRR”;G1-D2E2)))
There one problem how ever when a new positive value is generated insted of a negative one the formula should add the the last positive latest value to the new one. Rather then just starting allover again. This Illustrates the principle again:
So in G7 there sould be 21170 (G3+G7) not 20868.
Is there a way to achieve this?
Yes, there is such a way and it is not difficult. Just add the formula to cell H2
=SUM($G$1:$G2)
(Be careful with the $ sign - $G$1 is two characters, and $G2 is only one, before the letter, before the number it is not!)
Stretch the formula down - for each row you have a running total.
If you want to show values not for all rows, but only where the sum has changed, then complicate the formula a little:
=IF(G2;SUM($G$1:$G2);"")
Thanks! And since column G can be hidden it looks perfect to even tough its not in one formula.
Because the * asterisk is used for inline formatting of text in italics or bold and so for literal code you have to format that using either single ` backticks before and after a short portion of code, or use ``` triple backticks on separate lines before and after code. Formatting as code also prevents automatic typographic quotation marks instead of ASCII quote characters in the display of text. See also This is the guide - How to use the Ask site? - #6 by erAck .
Thanks for the information
Actually this function: =SUM($G$1:$G2)
Did not do what I hopped for. As it add up the entire G. column it will not provide a relevant sum because every new row in the G column is already a calculation of preceding rows .This is not apparent by the picture I put up. But but can be seen in this function.
=MAX(0; IF(B21="RRR";G20+D21*E21;IF(C21="RRR";G20-D21* E21
I suppose the formal abow could also be alters to work along with this one: =SUM($G$1:$G2). But that might case other problems. So hopefully there more elegant solution that solves he problem by altering one of the functions below?
=SUM($G$1:$G2)
=MAX(0; IF(B21="RRR";G20+D21*E21;IF(C21="RRR";G20-D21* E21
I returnerad to this lite projekt after a brak and have now spent a all nigit trying to fix it.
The objektive is as explaind abow to make a formula or a combination of formulas that skipps irrelevant rows (that doset have RRR in A or B), that adds or subtracts the rows with RRR in A or B.
Last a 0 should be generated in the palace of a negative number:
The problem here is that the gap betwean the rows mess up the chain of adding or subtracting the rows with the preceding one.
So far the sugestion whas to add the fist formula below to the secound one
=MAX(0;SUM($G$1:$G2)
=MAX(0;OM(B2=“RRR”;G1+D2E2;OM(C2=“RRR”;G1-D2E2)))
This will not work however becouse this will creat an irrelevant sum becose the first formula already sums up values (it will be a sum of a sum if you like).
My attempt to deal with this is to replace the secound formula with this one:
=MAX(0;OM(B2=“RRR”;G1+D2E2;OM(C2=“RRR”;G1-D2E2)))
This formula will unlike the first one creat positiv or negativ value and not add upp the numbers meaning that it might work with this one:
=MAX(0;SUM($G$1:$G2)
And this combination do work in many ways. As it adds upp relvant number from the first row and genrates a zero insted of neagtiv value. The remaning probklem is that this formula: =MAX(0;SUM($H$1:$H2)). Vill not start over agin once the 0 is genreated but will keep using the entiere row. as shown in te example.
what it sould do in the case is to start over and the numbers 4678,4 and 2694,8 but it will not becous the row as a total is still negativ
I tried more or less silly solutions alll night including this one:
MAX(0;SUM(H2:H3;IF(I3=0;I2))
Is there solution to this problmen. It would be great if some one could provide it. At this point after hours of attempts am even curious for more than practical reasons. Affcourse other formulas is just as good as long as it salves the problem.
I returnerad to this lite projekt after a brak and have now spent a all nigit trying to fix it.
The objektive is as explaind abow to make a formula or a combination of formulas that skipps irrelevant rows (that doset have RRR in A or B), that adds or subtracts the rows with RRR in A or B.
Last a 0 should be generated in the palace of a negative number:
The problem here is that the gap betwean the rows mess up the chain of adding or subtracting the rows with the preceding one.
So far the sugestion whas to add the fist formula below to the secound one
=MAX(0;SUM($G$1:$G2)
=MAX(0;OM(B2=“RRR”;G1+D2E2;OM(C2=“RRR”;G1-D2E2)))
This will not work however becouse this will creat an irrelevant sum becose the first formula already sums up values (it will be a sum of a sum if you like).
My attempt to deal with this is to replace the secound formula with this one:
=MAX(0;OM(B2=“RRR”;G1+D2E2;OM(C2=“RRR”;G1-D2E2)))
This formula will unlike the first one creat positiv or negativ value and not add upp the numbers meaning that it might work with this one:
=MAX(0;SUM($G$1:$G2)
And this combination do work in many ways. As it adds upp relvant number from the first row and genrates a zero insted of neagtiv value. The remaning probklem is that this formula: =MAX(0;SUM($H$1:$H2)). Vill not start over agin once the 0 is genreated but will keep using the entiere row. as shown in te example.
what it sould do in the case is to start over and the numbers 4678,4 and 2694,8 but it will not becous the row as a total is still negativ
I tried more or less silly solutions alll night including this one:
MAX(0;SUM(H2:H3;IF(I3=0;I2))
Is there solution to this problmen. It would be great if some one could provide it. At this point after hours of attempts am even curious for more than practical reasons. Affcourse other formulas is just as good as long as it salves the problem.
sorry I forgoth to uppload. This exampel ilustrates the problem.
formula: =MAX(0;SUM($H$1:$H2)). Vill not start over agin once the 0 is genreated but will keep using the entiere row. as shown in te example.
what it sould do in the case is to start over and the numbers 4678,4 and 2694,8 but it will not becous the row as a total is still negativ
There might be a completely different solution to this that doesn’t involve the formulas:
=MAX(0;SUM($G$1:$G2)
=MAX(0;OM(B2=“RRR”;G1+D2E2;OM(C2=“RRR”;G1-D2E2)))
Please read the last sentence again - “summation with the previous line creates a problem”. This is already the answer to your question: just remove the previous value from the formula, throw out G1 altogether.
Just write the simple phrase in the Calc function language
“If RRR is in column B of the current row, then the value from column D of the same row multiplied by the value from column E of the same row, otherwise if RRR is in column C of the current row, then the same product with a minus sign ELSE 0”.
That’s all, this is enough to get one column with values positive, negative and zero. Now, in the next column, do the summation with accumulation, hide the auxiliary column.
Im sure you saying somthing smart that I dont get. I acturly made a typ error but it seams as if youre in bord any way.
This will work:
“=IF(B69=“SSWM”;D69 * E69;IF(C69=“SSWM”;D69 * E69*-1))”
“=MAX(0;SUM($H$1:$H2)”
I tride this before.
But it works only as long as colum I in the picture abow is possitve. When a 0 is generated it sould start over again and not use the entier row agin. For exampel In the H colum in the picture there is one cell with -100 0000 in it. this would normaly generat a negativ number in the I colum but the fomrula deals with this and insted provide a 0.
Thats is exactly what I whant. However i the cell in colum I that coresponds to the cell in colum H with 4678,8 in it. There soud not be a 0 but 4678,8 . And in the next cell below that value the formula soul add 4678,4 and 2694,8.
Insted there are 0 all the way untill the H comum as a whole is positiv agin. The reson is ofcource that the formula uses the intire H comum in every new calculatuon. When it sould start over after a 0 has bin generated.
Is there any way to fix this?
Perhaps thers a wat to compensat so that colum H as a whole never getts negativ. Thats seams to be a a strange solution tough.
Tride to deleat this post.
Just would like to add that this one actually works perfectly:
=MAX(0;$L58+$K59)
It whas a unexpected soultion to me.
In conjunction with this one offcouce:
=MAX(0;OM(B2=“RRR”;G1+D2E2;OM(C2=“RRR”;G1-D2E2)))