Good day,
I have a table with data that contains sales per department per each week in the following format:
Week1 Week2 Week3 ...
Dept1 10 20 10
Dept1 20 10 30
Dept1 30 30 20
Dept2 20 20 30
Dept2 20 20 10
Dept3 50 40 60
...
What I need to do is to create a smaller report that will sum up the sales per department. As per following template:
Week1 Week2 Week3
Dept1 60 60 60
Dept2 40 40 40
Dept3 50 40 60
Total 150 140 160
The number of rows per department varies. Then this report should be printed on the spreadsheet.
From what I understand, this is possible to be done using dictionaries or collections. So far I have managed to calculate the sums per each week, however, I don't understand how to transfer these results to the worksheet. I have tried transferring the sums to the array but it didn't work.
This is the code I have so far. It correctly calculates the sums per week, then empties the collection and calculates it again for the following week. So, the main problem I have is how to write these results to the worksheet.
Dim collection As collection
Dim dataitems As Itemlist 'defined in classmodule
Dim key As String
Dim item As Double
Dim row As Long, column As Long
Dim lstrow As Long, lstcolumn As Long
Set collection = New collection
columnindex = 3 'that is the column where name of departments appear
lstrow = Sheet1.Cells(Sheet1.Rows.Count, column).End(xlUp).row
lstcolumn = Sheet1.Cells(1, Sheet1.Columns.Count).End(xlToLeft).column
For column = 5 To lstcolumn 'column 5 is where the weekly data start
For row = 2 To lstrow 'first 1 contains titles
key = CStr(Sheet1.Cells(row, "C").Value2)
item = CDbl(Sheet1.Cells(row, column).Value2)
Set dataitems = Nothing: On Error Resume Next
Set dataitems = collection(key): On Error GoTo 0
If dataitems Is Nothing Then
Set dataitems = New Itemlist
dataitems.key = key
collection.Add dataitems, key
End If
With dataitems
.Sum = .Sum + item
.Itemlist.Add item
End With
Next
Set collection = New collection
Next
Any help is appreciated. Thank you.