automating calculations and adding to existing table

[tabatha]

Hi, Beginner at VBA here. I am trying to automate some reports where the calculations are done and placed into the respective categories. I have a source data sheet:

24424

and would like to place it in this template sheet, by consolidating according to the unique type which looks like this:

24425

So far, I'm only able to get the types and their totals, but stuck at the count, average, min and max.
By using a dictionary, I would also like to add more values where I could calculate these and put them into the template sheet.
I followed and modified code by Paul Kelly (https://excelmacromastery.com/vba-dictionary/) and was able to produce this output:

24426

The code looks something like this:

Sub ForDictionary_Assign_Sum()

' PART 1 - Read the data
' Get the worksheets
Dim shRead As Worksheet
Set shRead = ThisWorkbook.Worksheets("Sheet1")

' Get the range
Dim rg As Range
Set rg = shRead.Range("B2").CurrentRegion



' Create the dictionary
Dim dict As New Dictionary


' Read through the data
Dim i As Long, types As String
Dim Average As Long

For i = 3 To rg.Rows.Count ' Store the values in a variable
types = rg.Cells(i, 2).Value2
Total = rg.Cells(i, 7).Value2

'Average = Application.WorksheetFunction.Average(.Range(.Cells(i, 7)))

dict(types) = dict(types) + Total



Next i

' PART 2 - Write the data
Dim shWrite As Worksheet
Set shWrite = ThisWorkbook.Worksheets("Sheet3")

With shWrite

' Clear the data in output worksheet
.Cells.ClearContents

' Write header
.Cells(1, 1).Value2 = "type"
.Cells(1, 2).Value2 = "Total"
.Cells(1, 3).Value2 = "Average"


End With

Dim key As Variant, row As Long
row = 2

'Read through each item in the Dictionary
For Each key In dict.Keys


shWrite.Cells(row, 1) = key
shWrite.Cells(row, 2) = dict(key)
shWrite.Cells(row, 3) = Average
row = row + 1

Next key

End Sub

Any help would be appreciated where I could make improvements and learn.
Thank you !

[p45cal]

Save us some effort; provide a file with some sample source data to experiment on, ideally with a sample of output too.

[tabatha]

Hi p45cal! thanks for your response. I have attached a sample WB here.


"Sheet1" contains the source data.
"Sheet3" contains the output that I get with the current code that I have edited.
The expected output is in sheet "Template(output)" where depending on the term period, the values are entered in the table.


24435

[p45cal]

I've not added any code yet because I'm not really sure about a few things.
What do the 1 month, 3 months, 6 months and 12+ months in the top line of the Template sheet refer to in the source table? And Future??
To take the donkey work out of the calculations I've pressed a pivot table into service, just to get the values.
See attachment.
Its source data is your Sheet1. That source data I've turned into an Excel Table, so that the pivot table's source data expands and collapses according to the size of the table automatically. It's called Table1.
I've added 3 rows of data to that table (K Type A), refreshed the pivot so that it appears in the pivot.
Your template table gets its values from the pivot table using GETPIVOTDATA formulas.
How to add to the template the new data? Manually first, later I can code for it:
First (on sheet Template(output)), copy the last three rows of your template (A11:L13) and paste to cell A14.
Then use the formula in A14: =O8
That's it.

Am I on the right track?

[tabatha]

Hi p45cal, thanks again for your reply!
What do the 1 month, 3 months, 6 months and 12+ months in the top line of the Template sheet refer to in the source table? And Future??

These are the term period criteria that I have to take into consideration while generating the report. For example: if the term period is more than 12 months, the subsequent item will fall under "Future".

To take the donkey work out of the calculations I've pressed a pivot table into service, just to get the values.

Am I on the right track?

Yes, you are on the right track, the pivot table actually is a lot of help. Thank you for this. :) I hope my existing code can help with getting new data into the template.
I have also been trying to update the code to generate the new row as is, so for now it auto-fills the color with yellow for the column.

[tabatha]

P45cal, I also had a question about how to add another category. For example: In the "Sheet Template(output)" pivot table, if I wanted to add item types with a term period of 2 months as a category, how would I be able to go about this?

Thanks :)

[p45cal]

In the pivot table in the previous attachment I had guess-grouped the Term Periods into a field called Term Period2 as shown below:
24515
All you need to do is redo the grouping or use Term Period instead of Term Period2 both in the pivot table and in the GetPivotData formulae.

Note also that I made a tweak to the formulae in column H of Sheet1:
=DATEDIF(D2,E2+1,"m")

[tabatha]

Thanks p45cal! I was able to get a solution for the problem by extending the current template and pulling in the values using the GetPivotData formula that was mentioned. :bow: