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 |