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

  • Hello,


    What about attaching an illustrative file ...

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