Consulting

Results 1 to 8 of 8

Thread: automating calculations and adding to existing table

  1. #1
    VBAX Regular
    Joined
    Jun 2019
    Posts
    6
    Location

    automating calculations and adding to existing table

    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:

    source data.jpg

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

    template.jpg

    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:

    what i got.PNG

    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 !

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Save us some effort; provide a file with some sample source data to experiment on, ideally with a sample of output too.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Regular
    Joined
    Jun 2019
    Posts
    6
    Location

    Post

    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.


    Sample WB.xlsm

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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?
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    VBAX Regular
    Joined
    Jun 2019
    Posts
    6
    Location
    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.

  6. #6
    VBAX Regular
    Joined
    Jun 2019
    Posts
    6
    Location
    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

  7. #7
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    In the pivot table in the previous attachment I had guess-grouped the Term Periods into a field called Term Period2 as shown below:
    2019-06-27_211701.jpg
    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")
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #8
    VBAX Regular
    Joined
    Jun 2019
    Posts
    6
    Location
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •