Re: [libreoffice-users] SUM, SUMIF, IF, INDEX, MATCH, VLOOKUP?

Reply without the other stuff since this is somewhat long.
Wanted to try to create a macrothat would do everthing 
with just the original data in columns A-D.


With old Quattro pro would have been amuch simpiler 
process.. Would be nice if we could upload sheets.


Well, just wanted to see if I could do something.


The LibreOffice Macro recorder is terrible. So had to do a 
lot. Seems to work, but not pretty. Found that something 
that use to work no longer does. Have an old macro that 
actual used a regular sort with options, but now recorder 
just makes a single rem line that does nothing. Does work 
with a just sort ascending record, so had to switch data 
and total columns.
With column A having the Date and Time value
and Columns B-D having the values.
Macro will do the Integer Date Only in Column E.
Puts the Date in Column F with the last line for a date
Puts the Total in Column G for that data.
Copies the data from F and G to H and I without blank 
lines and sorts it. If data was already sorted that line 
might not be needed?? It also sets the formatting.


I'm not following the latest message that refers to a 
Sheet2? Will have to look at it more.


Saved macro as
sub subtotal
rem ----------------------------------------------------------------------
rem define variables
dim documentas object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document= ThisComponent.CurrentController.Frame
dispatcher = createUnoService(com.sun.star.frame.DispatchHelper)


rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name =ToPoint
args1(0).Value =$E$1:$H$50


dispatcher.executeDispatch(document,.uno:GoToCell,, 0, args1())


rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document,.uno:Cut,, 0, Array())


rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name =ToPoint
args2(0).Value =$E$1


dispatcher.executeDispatch(document,.uno:GoToCell,, 0, args2())


rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name =StringName
args3(0).Value =Date Only
dispatcher.executeDispatch(document,.uno:EnterString,, 0, args3())


dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name =ToPoint
args4(0).Value =$E$2


dispatcher.executeDispatch(document,.uno:GoToCell,, 0, args4())


rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name =StringName
args5(0).Value ==if(a20,int(a2),+chr$(34)+chr$(34)+)
dispatcher.executeDispatch(document,.uno:EnterString,, 0, args5())


dim args6(0) as new com.sun.star.beans.PropertyValue
args6(0).Name =ToPoint
args6(0).Value =$F$2


dispatcher.executeDispatch(document,.uno:GoToCell,, 0, args6())


dim args7(0) as new com.sun.star.beans.PropertyValue
args7(0).Name =StringName
args7(0).Value ==IF(E2E3,E2,+CHR$(34)+CHR$(34)+)


dispatcher.executeDispatch(document,.uno:EnterString,, 0, args7())




dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name =ToPoint
args8(0).Value =$G$2


dispatcher.executeDispatch(document,.uno:GoToCell,, 0, args8())


rem ----------------------------------------------------------------------
dim args9(0) as new com.sun.star.beans.PropertyValue
args9(0).Name =StringName
args9(0).Value 
==IF(E2E3,SUMIFS($C$2:$C$50,$E$2:$e$50,+CHR$(34)+=+CHR$(34)+INT($A$2:$A$50)),+CHR$(34)+CHR$(34)+)


dispatcher.executeDispatch(document,.uno:EnterString,, 0, args9())




dim args10(0) as new com.sun.star.beans.PropertyValue
args10(0).Name =ToPoint
args10(0).Value =$E$2:$G$2


dispatcher.executeDispatch(document,.uno:GoToCell,, 0, args10())


rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document,.uno:Copy,, 0, Array())


dim args11(0) as new com.sun.star.beans.PropertyValue
args11(0).Name =ToPoint
args11(0).Value =$E$3:$G$50


dispatcher.executeDispatch(document,.uno:GoToCell,, 0, args11())


rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document,.uno:Paste,, 0, Array())


dim args12(0) as new com.sun.star.beans.PropertyValue
args12(0).Name =ToPoint
args12(0).Value =$E$2:$F$50
dispatcher.executeDispatch(document,.uno:GoToCell,, 0, args12())


rem ----------------------------------------------------------------------
dim args13(0) as new com.sun.star.beans.PropertyValue
args13(0).Name =NumberFormatValue
args13(0).Value = 84


dispatcher.executeDispatch(document,.uno:NumberFormatValue,, 0, 
args13())


rem ----------------------------------------------------------------------
rem Raw Data downloaded and pasted from 
https://www.worldometers.info/coronavirus/country/us/
dim args14(0) as new com.sun.star.beans.PropertyValue
args14(0).Name =ToPoint
args14(0).Value =$F$2:$G$60
dispatcher.executeDispatch(document,.uno:GoToCell,, 0, args14())


rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document,.uno:Copy,, 0, Array())


rem Raw Data downloaded and pasted from 
https://www.worldometers.info/coronavirus/country/us/
dim args15(0) as new com.sun.star.beans.PropertyValue
args15(0).Name =ToPoint
args15(0).Value =$H$2
dispatcher.executeDispatch(document,.uno:GoToCell,, 0, args15())


rem ----------------------------------------------------------------------
dim args16(5) as new com.sun.star.beans.PropertyValue
args16(0).Name =Flags
args16(0).Value =SVD
args16(1).Name =FormulaCommand
args16(1).Value = 0
args16(2).Name =SkipEmptyCells
args16(2).Value = false
args16(3).Name =Transpose
args16(3).Value = false
args16(4).Name =AsLink
args16(4).Value = false
args16(5).Name =MoveMode
args16(5).Value = 4


dispatcher.executeDispatch(document,.uno:InsertContents,, 0, args16())
rem Raw Data downloaded and pasted from 
https://www.worldometers.info/coronavirus/country/us/
dim args17(0) as new com.sun.star.beans.PropertyValue
args17(0).Name =ToPoint
args17(0).Value =$H$2:$I$50
dispatcher.executeDispatch(document,.uno:GoToCell,, 0, args17())


dispatcher.executeDispatch(document,.uno:SortAscending,, 0, Array())


dim args18(0) as new com.sun.star.beans.PropertyValue
args18(0).Name =ToPoint
args18(0).Value =$H$2:$H$50
dispatcher.executeDispatch(document,.uno:GoToCell,, 0, args18())


rem ----------------------------------------------------------------------
dim args19(0) as new com.sun.star.beans.PropertyValue
args19(0).Name =NumberFormatValue
args19(0).Value = 84


dispatcher.executeDispatch(document,.uno:NumberFormatValue,, 0, 
args19())


end sub


Is setup to handle up to data in A2 to A50, but could extend macro to do bigger 
range easily.


Data as displayed - Added a few more lines of data.
Date/Time , Value 1, Value Y, Value z,Date Only,,,,
2021-10-08 08:00:00,5,7,0.15,2021-10-08,,,2021-10-08,17
2021-10-08 14:05:16,3,10,5,2021-10-08,2021-10-08,17,2021-10-09,12
2021-10-09 10:05:30,15,3,25,2021-10-09,,,2021-10-13,22
2021-10-09 18:00:00,15,9,6,2021-10-09,2021-10-09,12,2021-10-14,99
2021-10-13 08:00:00,5,7,0.15,2021-10-13,,,,
2021-10-13 14:05:16,3,15,5,2021-10-13,2021-10-13,22,,
2021-10-14 10:05:30,15,87,25,2021-10-14,,,,
2021-10-14 18:00:00,15,12,6,2021-10-14,2021-10-14,99,,


Data with formulas
Date/Time , Value 1, Value Y, Value z,Date Only,,,,
2021-10-08 
08:00:00,5,7,0.15,=IF(A20,INT(A2),),=IF(E2E3,E2,),=IF(E2E3,SUMIFS($C$2:$C$50,$E$2:$E$50,=INT($A$2:$A$50)),),2021-10-08,17
2021-10-08 
14:05:16,3,10,5,=IF(A30,INT(A3),),=IF(E3E4,E3,),=IF(E3E4,SUMIFS($C$2:$C$50,$E$2:$E$50,=INT($A$2:$A$50)),),2021-10-09,12
2021-10-09 
10:05:30,15,3,25,=IF(A40,INT(A4),),=IF(E4E5,E4,),=IF(E4E5,SUMIFS($C$2:$C$50,$E$2:$E$50,=INT($A$2:$A$50)),),2021-10-13,22
2021-10-09 
18:00:00,15,9,6,=IF(A50,INT(A5),),=IF(E5E6,E5,),=IF(E5E6,SUMIFS($C$2:$C$50,$E$2:$E$50,=INT($A$2:$A$50)),),2021-10-14,99
2021-10-13 
08:00:00,5,7,0.15,=IF(A60,INT(A6),),=IF(E6E7,E6,),=IF(E6E7,SUMIFS($C$2:$C$50,$E$2:$E$50,=INT($A$2:$A$50)),),,
2021-10-13 
14:05:16,3,15,5,=IF(A70,INT(A7),),=IF(E7E8,E7,),=IF(E7E8,SUMIFS($C$2:$C$50,$E$2:$E$50,=INT($A$2:$A$50)),),,
2021-10-14 
10:05:30,15,87,25,=IF(A80,INT(A8),),=IF(E8E9,E8,),=IF(E8E9,SUMIFS($C$2:$C$50,$E$2:$E$50,=INT($A$2:$A$50)),),,
2021-10-14 
18:00:00,15,12,6,=IF(A90,INT(A9),),=IF(E9E10,E9,),=IF(E9E10,SUMIFS($C$2:$C$50,$E$2:$E$50,=INT($A$2:$A$50)),),,




-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy