Sum of values from unique string criterias [VBA]

[handels]

Hello

I have a very large dataset of unique funds, their sector and size. I have to sum up all the values for each sector in each fund. There are a lot of different string values so I need to use VBA to search for each unique string and add their corresponding together and create the output in a format like the one I have attached.

How would I go about doing this without the use of specified fund and sector string values to look for?

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG03Feb06
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] k [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] p [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]


   [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
   [COLOR="Navy"]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = 1
      [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
            [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]If[/COLOR] Not Dic(Dn.Value).exists(Dn.Offset(, 1).Value) [COLOR="Navy"]Then[/COLOR]
                Dic(Dn.Value).Add (Dn.Offset(, 1).Value), Dn.Offset(, 2)
            [COLOR="Navy"]Else[/COLOR]
                Dic(Dn.Value).Item(Dn.Offset(, 1).Value) = Dic(Dn.Value).Item(Dn.Offset(, 1).Value) + Dn.Offset(, 2)
            [COLOR="Navy"]End[/COLOR] If
     [COLOR="Navy"]Next[/COLOR] Dn
   
c = 2
Range("F1") = "Output"
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] Dic.keys
        Cells(c, "E") = k
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] p [COLOR="Navy"]In[/COLOR] Dic(k)
               Cells(c, "F") = p
               Cells(c, "G") = Dic(k).Item(p)
               c = c + 1
            [COLOR="Navy"]Next[/COLOR] p
            [COLOR="Navy"]If[/COLOR] Not k = "Fund" [COLOR="Navy"]Then[/COLOR] c = c + 1
    [COLOR="Navy"]Next[/COLOR] k
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[handels]

wow! Thank you so much, Mick!

It works superb.

 

[MickG]

You're welcome
Regrds Mick