Guinaba
Board Regular
- Joined
- Sep 19, 2018
- Messages
- 215
- Office Version
- 2016
- Platform
- Windows
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.
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
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.
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
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 |