VBA - Convert daily data to monthly data.

[Emeric]

21/01/2019	5
25/01/2019	9
01/02/2019	8
05/02/2019	2
12/03/2019	7

<tbody>
</tbody>

Good morning,

I do have a table presented as above.
On column "A" a range of daily date with the format: dd/mm/yy.
On column "B" a range of values.

what I want to do is to make a sum of values in column "B" conditionally on the month in column "A".
a example, for the month of January (01) it will be 14 (5+9), for the month of February (02), 10 (8+2).

The outcome must be a table with 2 columns :
One with the months (January, February, March...)
One with the corresponding sum (14, 10, 7...)

I was thinking about the use of a dictionary...

Any help would be so nice of you.

Thank you

 

[MickG]

With your data starting "A2", try this for results starting "C2"

[COLOR="Navy"]Sub[/COLOR] MG22Jan12
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
Txt = MonthName(Month(Dn.Value))
  [COLOR="Navy"]If[/COLOR] Not .Exists(Txt) [COLOR="Navy"]Then[/COLOR]
       .Add Txt, Dn.Offset(, 1).Value
    [COLOR="Navy"]Else[/COLOR]
        .Item(Txt) = .Item(Txt) + Dn.Offset(, 1).Value
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
Ray = Application.Transpose(Array(.Keys, .items))
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]With[/COLOR] Range("C2")
    .Offset(-1).Resize(, 2) = Array("Month", "Sum")
    .Resize(UBound(Ray, 1), 2) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Emeric]

Thank you Mick, you genius!

 

[MickG]

You're welcome