Sum unique categories in an array

[Certified]

I have an excel sheet with 24K rows of monetary transactions.

Each transaction is given a category; There are 6 different categories.

I stored the transactions into an array.

I want to sum each category and store them (category and total amount) into another array.

How would I go about doing that?

summary
SUM 24K transactions stored in an array by category and place the result into a new array.

 

[Eric W]

If the categories are in column A, and the amounts are in column B, then try:

=SUMIF(A:A,"Housing",B:B)

If you want your other array on a different sheet, and you have the categories in A1:A6, put this formula in B1

=SUMIF(Sheet1!A:A,A1,Sheet1!B:B)

and drag it down to B6.

 

[Certified]

Sorry, II am talking about VBA.

 

[Eric W]

Something like this could work:

Sub test3()
Dim MyArray(1000, 2), Results As Object
   
    Set Results = CreateObject("Scripting.Dictionary")
    
    For i = 1 To UBound(MyArray)
        Results(MyArray(i, 1)) = Results(MyArray(i, 1)) + MyArray(i, 2)
    Next i
    
    Range("A1").Resize(Results.Count) = WorksheetFunction.Transpose(Results.keys)
    Range("B1").Resize(Results.Count) = WorksheetFunction.Transpose(Results.items)
        
End Sub

but frankly, if your data starts on a sheet, the formulas would be better.

 

[Certified]

This works perfectly.

Two questions:
1. How do you create a dictionary with checking to see if the key EXISTs?

2. How could I save the results in an array?

Thanks for your help!

 

[Eric W]

Try:

Sub test3()
Dim MyArray(1000, 2), Results As Object, Array2(6, 2), i As Long, x As Variant
   
  
    Set Results = CreateObject("Scripting.Dictionary")
    
    For i = 1 To UBound(MyArray)
        Results(MyArray(i, 1)) = Results(MyArray(i, 1)) + MyArray(i, 2)
    Next i
    
    If Results.exists("Housing") Then Debug.Print "It's here"
    
    i = 1
    For Each x In Results
        Array2(i, 1) = x
        Array2(i, 2) = Results(x)
        i = i + 1
    Next x
    
End Sub

A dictionary is perfect for checking to see if a key exists, since you don't need to read through the entire array to see if it's there.

 

[Eric W]

One other way to move a dictionary to an array:

    MyKeys = Results.keys
    MyItems = Results.items

Each of those lines generates a 1-dimensional 0-based array of the contents of the dictionary. But if you want to put both the keys and items in a 2-D array, you'll need the code in the last post.