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: [email protected] 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
