title: Excel macro VBA to sum up duplicate values and then remove duplicate records
body: <p>I am trying to sum up values based on duplicate's found across "A-O" columns. Am using the below macro. There are around 500k+ records and the below macro hangs bad.</p>
  
  <pre><code> Sub Formulae(TargetCol1, TargetCol2, ConcatCol, Col1, Col2, StartRow, EndRow, Sheet)
  
           Sheets(Sheet).Range(TargetCol1 &amp; CStr(StartRow)).Formula = "=SUMIF($" &amp; ConcatCol &amp; "$" &amp; CStr(StartRow) &amp; ":$" &amp; ConcatCol &amp; "$" &amp; CStr(EndRow) &amp; "," &amp; ConcatCol &amp; CStr(StartRow) &amp; ",$" &amp; Col1 &amp; "$" &amp; CStr(StartRow) &amp; ":$" &amp; Col1 &amp; "$" &amp; CStr(EndRow) &amp; ")"
  
       Sheets(Sheet).Range(TargetCol1 &amp; CStr(StartRow)).Select
      Selection.Copy
      Sheets(Sheet).Range(TargetCol1 &amp; CStr(EndRow)).Select
      Range(Selection, Selection.End(xlUp)).Select
      Application.CutCopyMode = False
      Selection.FillDown
  
      Call PasteSpecial(TargetCol1, "T", StartRow, EndRow)
  
      Sheets(Sheet).Range(TargetCol2 &amp; CStr(StartRow)).Formula = "=SUMIF($" &amp; ConcatCol &amp; "$" &amp; CStr(StartRow) &amp; ":$" &amp; ConcatCol &amp; "$" &amp; CStr(EndRow) &amp; "," &amp; ConcatCol &amp; CStr(StartRow) &amp; ",$" &amp; Col2 &amp; "$" &amp; CStr(StartRow) &amp; ":$" &amp; Col2 &amp; "$" &amp; CStr(EndRow) &amp; ")"
  
       Sheets(Sheet).Range(TargetCol2 &amp; CStr(StartRow)).Select
      Selection.Copy
      Sheets(Sheet).Range(TargetCol2 &amp; CStr(EndRow)).Select
      Range(Selection, Selection.End(xlUp)).Select
      Application.CutCopyMode = False
      Selection.FillDown
  
      Call PasteSpecial(TargetCol2, "U", StartRow, EndRow)
  
  
   End Sub
  
  
  Sub PasteSpecial(Col1, Col2, StartRow, EndRow)
  
      Range(Col1 &amp; CStr(StartRow)).Select
      Range(Selection, Selection.End(xlDown)).Select
      Selection.Copy
      Range(Col2 &amp; CStr(StartRow)).Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
  End Sub
  </code></pre>
  
  <p>Let me explain the macro in short. I have Columns "A-O" and I have to group them...based on grouping I have to sum columns "P,Q". I have a function that makes a concatenated string out of the 16 columns and stores in "AA" column. Based on this column I use the sumif function to sum all duplicate values</p>
  
  <pre><code> =SUMIF($AA$2:$AA$500000,$AA2,$P$2:$P$500000)
   =SUMIF($AA$2:$AA$500000,$AA2,$Q$2:$Q$500000)
  </code></pre>
  
  <p>Then I copy paste special as 'values' the above values to remove the formula, in 2 new cols (pasteSpecial function in above macro code). </p>
  
  <p>Finally I call the remove duplicates to remove the duplicate values</p>
  
  <p>I have used the .removeduplicates method which seems to work pretty fast even on such a huge dataset. Is there any predefined function in excel which would even sum the values of the duplicates and then remove the duplicate entries?</p>
  
  <pre><code> Sub Remove_Duplicates_In_A_Range(StartRow, EndRow, Sheet, StartCol, EndCol, level)
  
  
  
  Sheets(Sheet).Range(StartCol &amp; CStr(StartRow) &amp; ":" &amp; EndCol &amp; CStr(EndRow)).RemoveDuplicates Columns:=20, Header:=xlNo
  
  End Sub
  </code></pre>
  
  <p>The above logic hangs bad eating all CPU resources and crashing badly...</p>
  
  <p>Someone please optimize the above macro to make it work with 500k+ records. A performance of 1-2 mins max is acceptable.</p>
  
  <p>Please help!!!</p>
  
  <p><strong>EDIT:</strong> By 500k+ records I mean A1:O500000. Am supposed to check for duplicates in this manner a combination of A1,B1,C1,D1,E1,F1,G1,H1,I1,J1,K1,L1,M1,N1,O1 with A2,B2,C2,D2,E2,F2,G2,H2,I2,J2,K2,L2,M2,N2,O2 and A3,B3,C3,D3,E3,F3,G3,H3,I3,J3,K3,L3,M3,N3,O3 and so on....till A500000,B500000 etc... .</p>
  
  <p>In short am supposed to check the entire A1-O1 set matches with the entire A2-O2 or A3-O3 or..... A500k-O500k and so on</p>
  
  <p>For every match between the entire A-O recordset I need to sum their respective P,Q columns . Say for example A1-O1 set matched with A2-O2 set then add P1,Q1 and P2,Q2 and store in P1,Q1 or something..</p>
  
  <p>In either case, I need to retain each original recordset say,A1-O1 with the summed up values of its duplicates and its own in P1,Q1</p>
  
  <p>I dont suppose we can attach a demo of the excel sheet here now, can we? :(</p>
  
  <p><strong>EDIT2:</strong></p>
  
  <p>Function for replicating sumif formula across all cells</p>
  
  <pre><code> Sub PreNettingBenefits(StartRow1, EndRow1, StartRow2, EndRow2, Col_Asset, Col_Liab, Src_Col_Asset, Src_Col_Liab, ConcatCol, Src_ConcatCol, level, Sheet2, Sheet1)
  
  '=SUMIF(Sheet1!$AA$2:$AA$81336,Sheet2!AA2,Sheet1!$P$2:$P$81336)
  Application.Calculation = xlCalculationAutomatic
  Sheets(Sheet2).Range(Col_Asset &amp; CStr(StartRow2)).Formula = "=SUMIF(" &amp; Sheet1 &amp; "!$" &amp; Src_ConcatCol &amp; "$" &amp; CStr(StartRow1) &amp; ":$" &amp; Src_ConcatCol &amp; "$" &amp; CStr(EndRow1) &amp; "," &amp; Sheet2 &amp; "!" &amp; ConcatCol &amp; CStr(StartRow2) &amp; "," &amp; Sheet1 &amp; "!$" &amp; Src_Col_Asset &amp; "$" &amp; CStr(StartRow1) &amp; ":$" &amp; Src_Col_Asset &amp; "$" &amp; CStr(EndRow1) &amp; ")"
  Sheets(Sheet2).Range(Col_Asset &amp; CStr(StartRow2)).Select
  Selection.Copy
  MsgBox Sheets(Sheet2).Range(Col_Asset &amp; CStr(EndRow2)).Address
  Sheets(Sheet2).Range(Col_Asset &amp; CStr(EndRow2)).Select
  Range(Col_Asset &amp; CStr(StartRow2) &amp; ":" &amp; Col_Asset &amp; CStr(EndRow2)).Select
  Application.CutCopyMode = False
  Selection.FillDown
  
  
  
  
  Sheets(Sheet2).Range(Col_Liab &amp; CStr(StartRow2)).Formula = "=SUMIF(" &amp; Sheet1 &amp; "!$" &amp; Src_ConcatCol &amp; "$" &amp; CStr(StartRow1) &amp; ":$" &amp; Src_ConcatCol &amp; "$" &amp; CStr(EndRow1) &amp; "," &amp; Sheet2 &amp; "!" &amp; ConcatCol &amp; CStr(StartRow2) &amp; "," &amp; Sheet1 &amp; "!$" &amp; Src_Col_Liab &amp; "$" &amp; CStr(StartRow1) &amp; ":$" &amp; Src_Col_Liab &amp; "$" &amp; CStr(EndRow1) &amp; ")"
  Sheets(Sheet2).Range(Col_Liab &amp; CStr(StartRow2)).Select
  Selection.Copy
  MsgBox Sheets(Sheet2).Range(Col_Liab &amp; CStr(EndRow2)).Address
  Sheets(Sheet2).Range(Col_Liab &amp; CStr(EndRow2)).Select
  Range(Col_Liab &amp; CStr(StartRow2) &amp; ":" &amp; Col_Liab &amp; CStr(EndRow2)).Select
  Application.CutCopyMode = False
  Selection.FillDown
  
  
  Application.Calculation = xlCalculationManual
  
  
  End Sub
  </code></pre>
  
  <p>It hangs pretty bad. Whts the problem in replicating the formula across 30k-40k rows. Could someone please optimise the code?</p>