Sum values in a column based on date

  • Hello,
    Could you please help creating a macro that sum up amount on Column C, then list the result on Column D, if the date on Column B is same?
    Below is part of a very long worksheet that I manually sum up and highlighted based on same date on Column B. Thank you for your HELP!
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 278"]

    [tr]


    [TD="class: xl63, width: 64"]Alpha[/TD]
    [TD="class: xl64, width: 179"]19-02-25[/TD]
    [TD="class: xl65, width: 64"] 12.99[/TD]
    [TD="class: xl66, width: 64"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]Alpha[/TD]
    [TD="class: xl64"]19-02-25[/TD]
    [TD="class: xl65"] 7.99[/TD]
    [TD="class: xl66"] 20.98[/TD]

    [/tr]


    [tr]


    [td]

    Alpha

    [/td]


    [TD="class: xl67"]19-03-03[/TD]
    [TD="class: xl68"] 15.99[/TD]
    [TD="class: xl69"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]Alpha[/TD]
    [TD="class: xl64"]19-03-09[/TD]
    [TD="class: xl65"] 3.44[/TD]
    [TD="class: xl66"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]Alpha[/TD]
    [TD="class: xl64"]19-03-09[/TD]
    [TD="class: xl65"] 4.99[/TD]
    [TD="class: xl66"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]Alpha[/TD]
    [TD="class: xl64"]19-03-09[/TD]
    [TD="class: xl65"] 14.99[/TD]
    [TD="class: xl66"] 23.42[/TD]

    [/tr]


    [tr]


    [td]

    Alpha

    [/td]


    [TD="class: xl67"]19-03-13[/TD]
    [TD="class: xl68"] 7.99[/TD]
    [TD="class: xl69"] [/TD]

    [/tr]


    [tr]


    [td]

    Alpha

    [/td]


    [TD="class: xl67"]19-03-14[/TD]
    [TD="class: xl68"] 18.99[/TD]
    [TD="class: xl69"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]Alpha[/TD]
    [TD="class: xl64"]19-03-25[/TD]
    [TD="class: xl65"] 22.00[/TD]
    [TD="class: xl66"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]Alpha[/TD]
    [TD="class: xl64"]19-03-25[/TD]
    [TD="class: xl65"] 19.99[/TD]
    [TD="class: xl66"] 41.99[/TD]

    [/tr]


    [tr]


    [td][/td]


    [TD="class: xl67"] [/TD]
    [TD="class: xl68"] [/TD]
    [TD="class: xl69"] [/TD]

    [/tr]


    [tr]


    [td][/td]


    [TD="class: xl67"] [/TD]
    [TD="class: xl68"] [/TD]
    [TD="class: xl69"] [/TD]

    [/tr]


    [tr]


    [td][/td]


    [TD="class: xl67"] [/TD]
    [TD="class: xl68"] [/TD]
    [TD="class: xl69"] [/TD]

    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [TD="class: xl67"]19-02-22[/TD]
    [TD="class: xl68"] 94.38[/TD]
    [TD="class: xl69"] [/TD]

    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [TD="class: xl67"]19-02-23[/TD]
    [TD="class: xl68"] 10.00[/TD]
    [TD="class: xl69"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl70"]Michael[/TD]
    [TD="class: xl71"]19-02-24[/TD]
    [TD="class: xl72"] 7.99[/TD]
    [TD="class: xl72"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl70"]Michael[/TD]
    [TD="class: xl71"]19-02-24[/TD]
    [TD="class: xl72"] 9.99[/TD]
    [TD="class: xl72"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl70"]Michael[/TD]
    [TD="class: xl71"]19-02-24[/TD]
    [TD="class: xl72"] 16.99[/TD]
    [TD="class: xl72"] 34.97[/TD]

    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [TD="class: xl67"]19-02-25[/TD]
    [TD="class: xl68"] 31.95[/TD]
    [TD="class: xl69"] [/TD]

    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [TD="class: xl67"]19-02-27[/TD]
    [TD="class: xl68"] 26.60[/TD]
    [TD="class: xl69"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]Michael[/TD]
    [TD="class: xl64"]19-02-28[/TD]
    [TD="class: xl65"] 17.95[/TD]
    [TD="class: xl65"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63"]Michael[/TD]
    [TD="class: xl64"]19-02-28[/TD]
    [TD="class: xl65"] 27.95[/TD]
    [TD="class: xl65"] 45.90[/TD]

    [/tr]


    [tr]


    [TD="class: xl70"]Michael[/TD]
    [TD="class: xl71"]19-03-01[/TD]
    [TD="class: xl72"] 20.00[/TD]
    [TD="class: xl72"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl70"]Michael[/TD]
    [TD="class: xl71"]19-03-01[/TD]
    [TD="class: xl72"] 8.60[/TD]
    [TD="class: xl72"] 28.60[/TD]

    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [TD="class: xl67"]19-03-02[/TD]
    [TD="class: xl68"] 12.99[/TD]
    [TD="class: xl69"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl70"]Michael[/TD]
    [TD="class: xl71"]19-03-03[/TD]
    [TD="class: xl72"] 13.55[/TD]
    [TD="class: xl72"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl70"]Michael[/TD]
    [TD="class: xl71"]19-03-03[/TD]
    [TD="class: xl72"] 9.55[/TD]
    [TD="class: xl72"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl70"]Michael[/TD]
    [TD="class: xl71"]19-03-03[/TD]
    [TD="class: xl72"] 3.99[/TD]
    [TD="class: xl72"] 27.09[/TD]

    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [TD="class: xl67"]19-03-04[/TD]
    [TD="class: xl68"] 12.99[/TD]
    [TD="class: xl69"] [/TD]

    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [TD="class: xl67"]19-03-05[/TD]
    [TD="class: xl68"] 15.99[/TD]
    [TD="class: xl69"] [/TD]

    [/tr]


    [/TABLE]

  • Hello and Welcome to the Forum ...:smile:


    Try to make your life easy ...


    Just Insert a Pivot Table and use the feature which allows to Group by Date ...


    Not only it will only take you 10 seconds ... but with the flexibility offered you will only need to refresh whenever data change ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hi Carim,
    Thank you for your solution! Up to this point where I want to sum up based on same date, I already run a macro that inserted 3 blank rows then add total amount for each individual account. However, I want to add more commands to this macro that sum the value in Column C based on same date in Column B. Then, list the result in Column D. Thanks again Carim!

  • Hi,


    If you are already using a macro to produce this report ...


    Why don't you post your macro ... to get a proposed modification ... ???


    Even better ... why don't you attach a sample file with your current macro ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hi Carim
    Here is the macro that I run:


    As you can see, after running this macro, I have to manually sum up the amount in Column C based on same date in Column B, then display the result in Column D. Thank you very much for your time!


    Dim lr As Long, r As Long, fr As Long
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Columns("B:B").Select
    Selection.Replace What:=" *", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    Selection.NumberFormat = "yy-mm-dd"
    Columns("B:B").Select
    Selection.ColumnWidth = 11
    Columns("B:D").Select
    With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 3).Sort _
    Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("C1"), _
    Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, _
    MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
    Columns("C:C").Select
    Selection.Cut
    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("E8").Select
    fr = 1
    lr = Range("A" & Rows.Count).End(xlUp).Row
    r = 1
    Do While r <= lr
    r = r + 1
    If Cells(r, 1).Value <> Cells(r - 1, 1).Value Then
    Rows(r).Resize(3).Insert
    Cells(r, 3).FormulaR1C1 = "=SUM(R" & fr & "C:R[-1]C)"
    r = r + 3
    fr = r
    lr = lr + 3
    End If
    Loop
    Application.ScreenUpdating = True
    End Sub

  • Hello,


    Thanks for your macro.


    Since there are many worksheet manipulations ( deleting Rows and Columns ...) it would make sense to attach a sample file .... :smile:


    By the way, with the exception of the sorting section ... most of the macro is generated by the macro recorder .. and deserves some improvement ... :wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hi Carim,below is the part of very long orginal file
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 239"]

    [tr]


    [TD="class: xl65, width: 64"]Alpha[/TD]
    [TD="width: 126"]2019-02-25 20:46:20.981076-08[/TD]
    [TD="width: 64"]AL000034[/TD]
    [TD="width: 64, align: right"]7.99[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, width: 64"]Alpha[/TD]

    [td]

    2019-03-03 14:06:01.27398-08

    [/td]


    [td]

    AL000035

    [/td]


    [TD="align: right"]15.99[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, width: 64"]Alpha[/TD]

    [td]

    2019-03-09 13:35:44.646709-08

    [/td]


    [td]

    AL000036

    [/td]


    [TD="align: right"]3.44[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, width: 64"]Alpha[/TD]

    [td]

    2019-03-09 16:32:11.686707-08

    [/td]


    [td]

    AL000037

    [/td]


    [TD="align: right"]4.99[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, width: 64"]Alpha[/TD]

    [td]

    2019-03-09 16:32:47.514488-08

    [/td]


    [td]

    AL000038

    [/td]


    [TD="align: right"]14.99[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, width: 64"]Alpha[/TD]

    [td]

    2019-03-13 19:02:43.313838-07

    [/td]


    [td]

    AL000039

    [/td]


    [TD="align: right"]7.99[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, width: 64"]Alpha[/TD]

    [td]

    2019-03-14 16:39:39.053635-07

    [/td]


    [td]

    AL000040

    [/td]


    [TD="align: right"]18.99[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, width: 64"]Alpha[/TD]

    [td]

    2019-03-25 20:34:06.389776-07

    [/td]


    [td]

    AL000041

    [/td]


    [TD="align: right"]22[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, width: 64"]Alpha[/TD]

    [td]

    2019-03-30 15:12:50.274899-07

    [/td]


    [td]

    AL000042

    [/td]


    [TD="align: right"]19.99[/TD]

    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-02-25 15:13:35.809738-08

    [/td]


    [td]

    MI000337

    [/td]


    [TD="align: right"]94.38[/TD]

    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-02-26 19:40:51.473973-08

    [/td]


    [td]

    MI000338

    [/td]


    [TD="align: right"]10[/TD]

    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-02-27 10:15:38.881493-08

    [/td]


    [td]

    MI000339

    [/td]


    [TD="align: right"]7.99[/TD]

    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-02-27 10:15:38.881493-08

    [/td]


    [td]

    MI000340

    [/td]


    [TD="align: right"]9.99[/TD]

    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-02-27 10:15:38.881493-08

    [/td]


    [td]

    MI000341

    [/td]


    [TD="align: right"]16.99[/TD]

    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-02-27 14:34:06.178665-08

    [/td]


    [td]

    MI000342

    [/td]


    [TD="align: right"]31.95[/TD]

    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-02-27 19:57:18.684453-08

    [/td]


    [td]

    MI000343

    [/td]


    [TD="align: right"]26.6[/TD]

    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-02-28 18:03:50.496687-08

    [/td]


    [td]

    MI000344

    [/td]


    [TD="align: right"]17.95[/TD]

    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-02-28 18:05:15.119825-08

    [/td]


    [td]

    MI000345

    [/td]


    [TD="align: right"]27.95[/TD]

    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-03-01 10:41:58.905819-08

    [/td]


    [td]

    MI000346

    [/td]


    [TD="align: right"]20[/TD]

    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-03-01 13:43:22.492583-08

    [/td]


    [td]

    MI000347

    [/td]


    [TD="align: right"]8.6[/TD]

    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-03-02 12:21:34.367801-08

    [/td]


    [td]

    MI000348

    [/td]


    [TD="align: right"]12.99[/TD]

    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-03-03 13:26:16.572658-08

    [/td]


    [td]

    MI000349

    [/td]


    [TD="align: right"]13.55[/TD]

    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-03-03 13:26:16.572658-08

    [/td]


    [td]

    MI000350

    [/td]


    [TD="align: right"]9.55[/TD]

    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-03-03 16:07:12.673826-08

    [/td]


    [td]

    MI000351

    [/td]


    [TD="align: right"]3.99[/TD]

    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-03-05 20:28:00.477949-08

    [/td]


    [td]

    MI000352

    [/td]


    [TD="align: right"]12.99[/TD]

    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-03-05 20:28:46.418232-08

    [/td]


    [td]

    MI000353

    [/td]


    [TD="align: right"]15.99[/TD]

    [/tr]


    [/TABLE]
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="class: xl63, width: 64"] [/TD]
    [TD="width: 126"] [/TD]
    [TD="width: 64"] [/TD]
    [TD="width: 64, align: right"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 64"] [/TD]

    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 64"] [/TD]

    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 64"] [/TD]

    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 64"] [/TD]

    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 64"] [/TD]

    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 64"] [/TD]

    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 64"] [/TD]

    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 64"] [/TD]

    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [/TABLE]

  • Hi Carim,
    The first row I missed is added [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 287"]

    [tr]


    [TD="width: 64"]Owners[/TD]
    [TD="width: 126"]Payment Time[/TD]
    [TD="width: 64"]Receipt Code[/TD]
    [TD="width: 128, colspan: 2"]Amount Paid[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, width: 64"]Alpha[/TD]

    [td]

    2019-02-25 20:46:20.981076-08

    [/td]


    [td]

    AL000034

    [/td]


    [TD="align: right"]7.99[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="class: xl65, width: 64"]Alpha[/TD]

    [td]

    2019-03-03 14:06:01.27398-08

    [/td]


    [td]

    AL000035

    [/td]


    [TD="align: right"]15.99[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="class: xl65, width: 64"]Alpha[/TD]

    [td]

    2019-03-09 13:35:44.646709-08

    [/td]


    [td]

    AL000036

    [/td]


    [TD="align: right"]3.44[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="class: xl65, width: 64"]Alpha[/TD]

    [td]

    2019-03-09 16:32:11.686707-08

    [/td]


    [td]

    AL000037

    [/td]


    [TD="align: right"]4.99[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="class: xl65, width: 64"]Alpha[/TD]

    [td]

    2019-03-09 16:32:47.514488-08

    [/td]


    [td]

    AL000038

    [/td]


    [TD="align: right"]14.99[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="class: xl65, width: 64"]Alpha[/TD]

    [td]

    2019-03-13 19:02:43.313838-07

    [/td]


    [td]

    AL000039

    [/td]


    [TD="align: right"]7.99[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="class: xl65, width: 64"]Alpha[/TD]

    [td]

    2019-03-14 16:39:39.053635-07

    [/td]


    [td]

    AL000040

    [/td]


    [TD="align: right"]18.99[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="class: xl65, width: 64"]Alpha[/TD]

    [td]

    2019-03-25 20:34:06.389776-07

    [/td]


    [td]

    AL000041

    [/td]


    [TD="align: right"]22[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="class: xl65, width: 64"]Alpha[/TD]

    [td]

    2019-03-30 15:12:50.274899-07

    [/td]


    [td]

    AL000042

    [/td]


    [TD="align: right"]19.99[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-02-25 15:13:35.809738-08

    [/td]


    [td]

    MI000337

    [/td]


    [TD="align: right"]94.38[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-02-26 19:40:51.473973-08

    [/td]


    [td]

    MI000338

    [/td]


    [TD="align: right"]10[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-02-27 10:15:38.881493-08

    [/td]


    [td]

    MI000339

    [/td]


    [TD="align: right"]7.99[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-02-27 10:15:38.881493-08

    [/td]


    [td]

    MI000340

    [/td]


    [TD="align: right"]9.99[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-02-27 10:15:38.881493-08

    [/td]


    [td]

    MI000341

    [/td]


    [TD="align: right"]16.99[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-02-27 14:34:06.178665-08

    [/td]


    [td]

    MI000342

    [/td]


    [TD="align: right"]31.95[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-02-27 19:57:18.684453-08

    [/td]


    [td]

    MI000343

    [/td]


    [TD="align: right"]26.6[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-02-28 18:03:50.496687-08

    [/td]


    [td]

    MI000344

    [/td]


    [TD="align: right"]17.95[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-02-28 18:05:15.119825-08

    [/td]


    [td]

    MI000345

    [/td]


    [TD="align: right"]27.95[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-03-01 10:41:58.905819-08

    [/td]


    [td]

    MI000346

    [/td]


    [TD="align: right"]20[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-03-01 13:43:22.492583-08

    [/td]


    [td]

    MI000347

    [/td]


    [TD="align: right"]8.6[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-03-02 12:21:34.367801-08

    [/td]


    [td]

    MI000348

    [/td]


    [TD="align: right"]12.99[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-03-03 13:26:16.572658-08

    [/td]


    [td]

    MI000349

    [/td]


    [TD="align: right"]13.55[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-03-03 13:26:16.572658-08

    [/td]


    [td]

    MI000350

    [/td]


    [TD="align: right"]9.55[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-03-03 16:07:12.673826-08

    [/td]


    [td]

    MI000351

    [/td]


    [TD="align: right"]3.99[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-03-05 20:28:00.477949-08

    [/td]


    [td]

    MI000352

    [/td]


    [TD="align: right"]12.99[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    Michael

    [/td]


    [td]

    2019-03-05 20:28:46.418232-08

    [/td]


    [td]

    MI000353

    [/td]


    [TD="align: right"]15.99[/TD]

    [td][/td]


    [/tr]


    [/TABLE]

  • Try the attached file. Click the button to generate Monthly Totals for each Owner.


    Code assigned to the button:


    The first bit of the code is to ensure that the data is sorted by Owner and Payment Time.


    Note you must include the new header in cell E1 when transferring the code to your actual data.

  • KjBox


    Hello,


    Nice Macro ... :smile:


    Obviously do not know why ... but for any OP .... attaching a sample file remains a psychological hurdle .....


    Even if I am still convinced a Pivot Table would produce the expected results in less than 5 seconds ... does not matter ...


    Question : why .Value2 ?


    Thought Array variants were not format sensitive ...


    Could it prevent local configuration conflicts with Dates ?


    Thanks for your insight ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Hi Carim,


    Thanks.


    Value2 gives a cells underlying value (so dates as their Long values). Then by using CDate in the code that date is converted to Date format in whatever Locale settings the user has, this overcomes the issue of code been written using a particular Locale setting then that code failing for a user with a different Locale setting.


    In actual fact, .Value2 is better than .Value for any data type in over 90% of cases.


    For a full explanation of the differences between .Text, .Value and .Value2 see https://fastexcel.wordpress.com/2011...w-to-avoid-it/


    You are right about using a Pivot Table, but the OP asked for code to produce the illustrated result, maybe that works better for him in his situation.


    Having said that, attached is the sample file with the data in an Excel Built-in table, my code modified to suit the new table format and a Pivot Table that gives the same results.


    I have added code in the Worksheet Object Module which will update the Pivot Table when change(s) to the data are made

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, [tblPayments]) Is Nothing Then ThisWorkbook.RefreshAll
    End Sub
  • Hi Charles :smile:


    Thanks for the link to Charles ' site ...


    Interesting to learn that Value2 is faster Value ... !!!


    Take care


    :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Value2 was added to Excel around 10 years ago, but Value remained (and still does) as the default property of a range. IMO the default should be Value2.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Value2 was added to Excel around 10 years ago, but Value remained (and still does) as the default property of a range. IMO the default should be Value2.


    You are right ...!!!


    No doubt about this ...


    Thanks again


    :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Thank you so much for your time KjBox and Carim!
    I created a blank macro then pasted your code. However, I got the message:"Run time error (9), Subscript out of range. And, when I hit debug, it highlighted
    x(i, 5) = dTot + x(i, 4) Am I doing somethings wrong? Greatly appreciate your help!

  • Hello,


    As KjBox pointed out .... before launching the macro ... make sure to have a Header in cell E1 ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Thanks Carim, I was going to suggest the same thing! Most likely source of the error.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Thank you for all your help! I greatly appreciate KjBox and Carim who have dedicated their precious time to help community!!! Just let you know that I have read all your solution but don't have time to try your solutions yet. Will let you updated. Just quick glance of the solution and get confused with: "before launching the macro ... make sure to have a Header in cell E1 ..."
    Header in cell E1...What exactly this mean Carim?

  • If you look at the test file I attached you will see that in cell E1 I put "Monthly Totals".


    You need to do the same to your actual file before running the macro.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi KjBox and Carim,


    As we discussed, I just list some of the owners to the list. The actual list is about 600 rows. So when I added more rows to the sample you provided, I got the message:"Run-time error '13' Type mismatch. Any advise? Thanks.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!