Summing and Grouping using Array

  • I have two arrays:

    RawData Array: having the raw data

    Calc Array: having the transformed data

    In the Calc array the column DEM needs to be summed grouping the data by column MTHID. Any suggestions, please.


    Code:


    Sub AddCalc()


    Dim RawData() As Variant

    Dim Calc() As Variant

    Dim Dim1 As Long, Counter As Long


    Sheet2.Activate


    'Add data range into the RawData array

    RawData = Range("A2", Range("A2").End(xlDown).End(xlToRight))


    Dim1 = UBound(RawData, 1)

    Dim2 = UBound(RawData, 2)


    ReDim Calc(1 To Dim1, 1 To Dim2)


    For Counter = 1 To Dim1

    Calc(Counter, 1) = RawData(Counter, 3)

    Calc(Counter, 2) = RawData(Counter, 16)

    Calc(Counter, 3) = RawData(Counter, 17)

    Calc(Counter, 4) = RawData(Counter, 6)


    Next Counter


    Worksheets.Add

    Range("A2", Range("A2").Offset(Dim1, 3)).Value = Calc

    [A1:D1] = [{"SKUCODE","MONTHYEAR","MTHID","DEM"}]


    Erase RawData

    Erase Calc


    End Sub



    Table:


    SKUCODE

    MONTHYEAR

    MTHID

    DEM

    2CSH00596-5005

    1/01/2020

    MTH1

    0

    2CSH00596-5005

    1/01/2020

    MTH1

    0

    2CSH00596-5005

    1/01/2020

    MTH1

    2

    2CSH00596-5005

    1/01/2020

    MTH1

    1

    2CSH00596-5005

    1/01/2020

    MTH1

    1

    2CSH00596-5005

    1/01/2020

    MTH1

    1

    2CSH00596-5005

    1/01/2020

    MTH1

    2

    2CSH00596-5005

    1/01/2020

    MTH1

    1

    2CSH00596-5005

    1/02/2020

    MTH2

    2

    2CSH00596-5005

    1/02/2020

    MTH2

    1

    2CSH00596-5005

    1/02/2020

    MTH2

    1

    2CSH00596-5005

    1/03/2020

    MTH3

    1

    2CSH00596-5005

    1/03/2020

    MTH3

    1

    2CSH00596-5005

    1/03/2020

    MTH3

    1

    2CSH00596-5005

    1/03/2020

    MTH3

    1

    2CSH00596-5005

    1/04/2020

    MTH4

    1

    2CSH00596-5005

    1/04/2020

    MTH4

    1

    2CSH00596-5005

    1/05/2020

    MTH5

    1

    2CSH00596-5005

    1/05/2020

    MTH5

    1

    2CSH00596-5005

    1/06/2020

    MTH6

    1

    2CSH00596-5005

    1/07/2020

    MTH7

    1

    2CSH00596-5005

    1/07/2020

    MTH7

    1

    2CSH00596-5005

    1/07/2020

    MTH7

    1

    2CSH00596-5005

    1/07/2020

    MTH7

    1

    2CSH00596-5005

    1/08/2020

    MTH8

    1

    2CSH00596-5005

    1/08/2020

    MTH8

    1

    2CSH00596-5005

    1/09/2020

    MTH9

    1

    2CSH00596-5005

    1/09/2020

    MTH9

    1

    2CSH00596-5005

    1/09/2020

    MTH9

    1

    2CSH00596-5005

    1/10/2020

    MTH10

    1

    2CSH00596-5005

    1/10/2020

    MTH10

    1

    2CSH00596-5005

    1/11/2020

    MTH11

    1

    2CSH00596-5005

    1/11/2020

    MTH11

    1

    2CSH00596-5005

    1/12/2020

    MTH12

    1

    2CSH00596-5005

    1/12/2020

    MTH12

    1