Full and Part Summaries with the Dictionary

Creating summaries using the scripting dictionary - I have touched on this issue in prior posts on both the main site and the blog.  It can be a little confusing to get your head around the scripting dictionary but here is some code which will help you on your journey.  I first saw this method years ago on Ozgrid by a poster named Jindon – he is rather famous in Excel circles as his posts are insightful and really fast and accurate.  The method takes a dataset and has the ability to summarise many thousands of rows instantly and when I say instantly it happens so fast when compared to other methods.

 

Let’s take a simple dataset and summarise it by a unique identifyer.  If we have a fruit stall and want to summaries the sales amount by items sold.

Scripting Dictionary

The above is a sample of the data which is a lot longer inside the file but will give you a feel for the data we are trying to summarise.

Summary with Dic

 

The summary by fruit would look a bit like the above.  The scripting dictionary was used to produce this simplistic summary.  The first method I will show you will generate a sum based on unique criteria.  The unique part being the name of the fruit. 


SubSumIt()
    Dim ar
    Dim i As Long
    Dim arr As Variant
    Dim n As Long
    
    ar = [a1].CurrentRegion.Value
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(ar, 1)
            .Item(ar(i, 1)) = .Item(ar(i, 1)) + ar(i, 6)
        Next
        arr = Array(.keys, .items)
        n = .Count
    End With
    [T4].CurrentRegion.ClearContents
    [T4].Resize(n, 2).Value = Application.Transpose(arr)
End Sub

The reason the above coding works is the dictionary will only include unique items.  The fruit is unique, so each item is assessed and summed in turn. 

.Item(ar(i, 1)) = .Item(ar(i, 1)) + ar(i, 6)

The 6 at the end of the line above refers to column 6 (the summation) in the example dataset.  Change the 6 to the column which is relevant for your data.

.Item(ar(i, 1)) = .Item(ar(i, 1)) + ar(i, 6)

Additionally the current items value is added with the addition of the next like value in the list.  When the loop is complete the entire contents of the dictionary is added to the variable arr.

arr = Array(.keys, .items)

Now all that needs to be done is to define a space to put the contents of the array (arr).

[T4].Resize(n, 2).Value = Application.Transpose(arr)

Where n is the length of the array and 2 is the width (two columns Fruit and the value we place against it.

 

The second method is to produce a summary but include each unique line of the table.

Excel Scripting dictionary

The above has more details about each of the items in the list.  You can see more details have been included in this type of summary.  This time Columns O and P have been summarised. 

 

Sub SumMultiple()
    Dim ar As Variant
    Dim i As Long
    Dim j As Long
    Dim n As Long
    Dim str As String
    
    n = 1
    ar = Cells(10, 1).CurrentRegion.Value
    With CreateObject("Scripting.Dictionary")
        For i = 2 To UBound(ar, 1)
            str = ar(i, 1)
            If Not .Exists(str) Then
                n = n + 1
                For j = 1 To UBound(ar, 2)
                    ar(n, j) = ar(i, j)
                Next
                .Item(str) = n
            Else
                For j = 5 To UBound(ar, 2)
                    ar(.Item(str), j) = ar(.Item(str), j) + ar(i, j)
                Next
            End If
        Next
    End With
    [K4].CurrentRegion.ClearContents
    [K4].Resize(n, UBound(ar, 2)).Value = ar
End Sub

The above has a lot more going on but all you really need to know is this:

 

                For j = 5 To UBound(ar, 2)
                    ar(.Item(str), j) = ar(.Item(str), j) + ar(i, j)
                Next

The columns to Sum start in Column 5 (E) and go to the end of the columns you need to sum in this case 6.  Now if the dataset was larger say 10 columns to sum it would sum these columns all in turn with the above loop starting in Column 5 summing all the like items and ending in Column 14.  It is very efficient and quite simple to adapt to your needs.  You will be remarkably surprised how fast it runs even on datasets which stretch into the thousands of rows.

 

SumWithDictionary.xlsm