title: Optimizing Multiple Collection operations in VBA
body: <p>I'm trying to optimize code that I use to sum certain values in a column based on some criteria. My columns are A (PersonID), B (Firm), and C (ValuetoSum). A very abridged version might look like this:</p>
  
  <pre><code>A             B              C
  1             BAML           100
  1             HSBC           150
  2             HSBC           110
  4             CITI           150
  5             HSBC           200
  </code></pre>
  
  <p>I want to loop through each firm in column B, find all of IDs of people that correspond to them and sum the all of values in column C corresponding to those IDs. So for HSBC, the code would collect the IDs 1 and 4 and then sum 130 + 100 + 120 = 460. </p>
  
  <p>I currently do this using multiple loops and collections that take a long time to run. The process is as follows:</p>
  
  <p>For each firm
  Create a collection of PersonID's based on criteria (firm and year)
  Create a collection of Values based on collection of person IDs and criteria (year)
  Sum all the values in the second collection
  next firm</p>
  
  <p>For those trying to navigate the code below: RP refers to a person and this portion of the code is interested in finding values for the trend year (last year). So TrendYearRPColl is Trend Year Research Partner Collection.</p>
  
  <pre><code>For i2 = 2 To LastRowUniqueClientList
  
     ActiveFirm = Cells(i2, UniqueClientListColNum).Value
  
  Set TrendYearRPColl = New Collection
  
      For i3 = 2 To LastRow
          If Cells(i3, DBFirmColNum).Value = ActiveFirm And Cells(i3, DBYearColNum).Value = TrendYear Then
                  TrendYearRPColl.Add Cells(i3, DBRespondentKeyColNum).Value
  
              End If
          Next i3
  
  
  Set TrendYearMktShareColl = New Collection
  
  
  For Each TrendYearRP In TrendYearRPColl
      For i7 = 2 To LastRow
          If Cells(i7, DBRespondentKeyColNum).Value = TrendYearRP And Cells(i7, DBYearColNum).Value = TrendYear Then
              TrendYearMktShareColl.Add Cells(i7, DBMktShareVolColNum).Value
  
          End If
      Next i7
  Next TrendYearRP
  
  
  For Each TrendYearMktShare In TrendYearMktShareColl
  
      TrendYearSum = TrendYearSum + TrendYearMktShare
  
  Next TrendYearMktShare
  </code></pre>
  
  <p>I am wondering if anybody here thinks it would be worth converting this operation to several worksheet functions to save calculation time. If it is worth it, I would also really appreciate a recommendation on the direction to take. I have put together a few ws functions that do the job, but they require adding in and writing to columns since I am not very adept with these formulas. </p>
  
  <p>Please let me know if anything needs to be explained better and thanks to anyone who takes a stab at this.</p>
  
  <p>-Steve</p>
  
  <p>Edited to show 460 as output.</p>