Summation Macro based on 2 Criteria

[boldcode]

Hello,


I have the following 3 worksheets in my excel file:


1. Before Macro – This worksheet represents the actual data that I will be importing into excel from an external data source


<table style="width: 958px; height: 427px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:1934;width:41pt" width="54"> <col style="mso-width-source:userset;mso-width-alt:2787;width:59pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:2759;width:58pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:1450;width:31pt" width="41"> <col style="mso-width-source:userset;mso-width-alt:2275;width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2218;width:47pt" width="62"> <col style="mso-width-source:userset;mso-width-alt:2190;width:46pt" width="62"> <col style="mso-width-source:userset;mso-width-alt:3982;width:84pt" width="112"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:14.4pt" height="19"> <td class="xl68" style="height:14.4pt;width:41pt" width="54" height="19">
</td> <td style="width:59pt" width="78">Column A</td> <td class="xl73" style="width:58pt" width="78">Column B</td> <td style="width:31pt" width="41">Column C</td> <td style="width:48pt" width="64">Column D</td> <td style="width:47pt" width="62">Column E</td> <td style="width:46pt" width="62">Column F</td> <td style="width:84pt" width="112">Column G</td> <td style="width:48pt" width="64">Column H</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 1</td> <td class="xl69">Report Date</td> <td class="xl64" style="border-left:none">EmployeeID</td> <td class="xl70" style="border-left:none">Name</td> <td class="xl64" style="border-left:none">BW</td> <td class="xl64" style="border-left:none">Class</td> <td class="xl64" style="border-left:none">Make</td> <td class="xl64" style="border-left:none">Function</td> <td class="xl65" style="border-left:none">Hours</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 2</td> <td class="xl74" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">4</td> <td class="xl63" style="border-top:none;border-left:none">Ben</td> <td class="xl63" style="border-top:none;border-left:none">3</td> <td class="xl63" style="border-top:none;border-left:none">NC 9</td> <td class="xl63" style="border-top:none;border-left:none">NC 9</td> <td class="xl63" style="border-top:none;border-left:none">Quality Assurance</td> <td class="xl63" style="border-top:none;border-left:none">9</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 3</td> <td class="xl74" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">3</td> <td class="xl63" style="border-top:none;border-left:none">Jen</td> <td class="xl63" style="border-top:none;border-left:none">5</td> <td class="xl63" style="border-top:none;border-left:none">NS 7</td> <td class="xl63" style="border-top:none;border-left:none">NS 7</td> <td class="xl63" style="border-top:none;border-left:none">Accounting</td> <td class="xl63" style="border-top:none;border-left:none">5</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 4</td> <td class="xl74" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">1</td> <td class="xl71" style="border-top:none;border-left:none">Mike</td> <td class="xl71" style="border-top:none;border-left:none">3</td> <td class="xl71" style="border-top:none;border-left:none">NC 9</td> <td class="xl71" style="border-top:none;border-left:none">NC 9</td> <td class="xl71" style="border-top:none;border-left:none">Quality Assurance</td> <td class="xl71" style="border-top:none;border-left:none">2</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 5</td> <td class="xl74" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">1</td> <td class="xl71" style="border-top:none;border-left:none">Mike</td> <td class="xl71" style="border-top:none;border-left:none">3</td> <td class="xl71" style="border-top:none;border-left:none">NC 9</td> <td class="xl71" style="border-top:none;border-left:none">NC 9</td> <td class="xl71" style="border-top:none;border-left:none">Quality Assurance</td> <td class="xl71" style="border-top:none;border-left:none">7</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 6</td> <td class="xl74" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">1</td> <td class="xl71" style="border-top:none;border-left:none">Mike</td> <td class="xl71" style="border-top:none;border-left:none">1</td> <td class="xl71" style="border-top:none;border-left:none">NC 1</td> <td class="xl71" style="border-top:none;border-left:none">NC 1</td> <td class="xl71" style="border-top:none;border-left:none">Finance</td> <td class="xl71" style="border-top:none;border-left:none">1</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 7</td> <td class="xl74" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">5</td> <td class="xl63" style="border-top:none;border-left:none">Scott</td> <td class="xl63" style="border-top:none;border-left:none">4</td> <td class="xl63" style="border-top:none;border-left:none">NS 4</td> <td class="xl63" style="border-top:none;border-left:none">NS 4</td> <td class="xl63" style="border-top:none;border-left:none">Engineering</td> <td class="xl63" style="border-top:none;border-left:none">6</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 8</td> <td class="xl74" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">2</td> <td class="xl72" style="border-top:none;border-left:none">Tim</td> <td class="xl72" style="border-top:none;border-left:none">1</td> <td class="xl72" style="border-top:none;border-left:none">NC 1</td> <td class="xl72" style="border-top:none;border-left:none">NC 1</td> <td class="xl72" style="border-top:none;border-left:none">Finance</td> <td class="xl72" style="border-top:none;border-left:none">4</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 9</td> <td class="xl74" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">2</td> <td class="xl72" style="border-top:none;border-left:none">Tim</td> <td class="xl72" style="border-top:none;border-left:none">1</td> <td class="xl72" style="border-top:none;border-left:none">NC 1</td> <td class="xl72" style="border-top:none;border-left:none">NC 1</td> <td class="xl72" style="border-top:none;border-left:none">Finance</td> <td class="xl72" style="border-top:none;border-left:none">8</td> </tr> </tbody></table>2. After Macro – This worksheet shows what the data looks like after I run the macro


<table style="width: 1007px; height: 336px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2787;width:59pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:2759;width:58pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:1507;width:32pt" width="42"> <col style="mso-width-source:userset;mso-width-alt:1223;width:26pt" width="34"> <col style="mso-width-source:userset;mso-width-alt:1792;width:38pt" width="50"> <col style="mso-width-source:userset;mso-width-alt:2190;width:46pt" width="62"> <col style="mso-width-source:userset;mso-width-alt:3982;width:84pt" width="112"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:14.4pt" height="19"> <td class="xl70" style="height:14.4pt;width:48pt" width="64" height="19">
</td> <td style="width:59pt" width="78">Column A</td> <td class="xl75" style="width:58pt" width="78">Column B</td> <td style="width:32pt" width="42">Column C</td> <td style="width:26pt" width="34">Column D</td> <td style="width:38pt" width="50">Column E</td> <td style="width:46pt" width="62">Column F</td> <td style="width:84pt" width="112">Column G</td> <td style="width:48pt" width="64">Column H</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 1</td> <td class="xl71">Report Date</td> <td class="xl66" style="border-left:none">EmployeeID</td> <td class="xl72" style="border-left:none">Name</td> <td class="xl66" style="border-left:none">BW</td> <td class="xl66" style="border-left:none">Class</td> <td class="xl66" style="border-left:none">Make</td> <td class="xl66" style="border-left:none">Function</td> <td class="xl67" style="border-left:none">Hours</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 2</td> <td class="xl76" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">4</td> <td class="xl65" style="border-top:none;border-left:none">Ben</td> <td class="xl65" style="border-top:none;border-left:none">3</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">Quality Assurance</td> <td class="xl65" style="border-top:none;border-left:none">9</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 3</td> <td class="xl76" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">3</td> <td class="xl65" style="border-top:none;border-left:none">Jen</td> <td class="xl65" style="border-top:none;border-left:none">5</td> <td class="xl65" style="border-top:none;border-left:none">NS 7</td> <td class="xl65" style="border-top:none;border-left:none">NS 7</td> <td class="xl65" style="border-top:none;border-left:none">Accounting</td> <td class="xl65" style="border-top:none;border-left:none">5</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 4</td> <td class="xl76" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl73" style="border-top:none;border-left:none">Mike</td> <td class="xl73" style="border-top:none;border-left:none">3</td> <td class="xl73" style="border-top:none;border-left:none">NC 9</td> <td class="xl73" style="border-top:none;border-left:none">NC 9</td> <td class="xl73" style="border-top:none;border-left:none">Quality Assurance</td> <td class="xl73" style="border-top:none;border-left:none">9</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 5</td> <td class="xl76" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl73" style="border-top:none;border-left:none">Mike</td> <td class="xl73" style="border-top:none;border-left:none">1</td> <td class="xl73" style="border-top:none;border-left:none">NC 1</td> <td class="xl73" style="border-top:none;border-left:none">NC 1</td> <td class="xl73" style="border-top:none;border-left:none">Finance</td> <td class="xl73" style="border-top:none;border-left:none">1</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 6</td> <td class="xl76" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">5</td> <td class="xl65" style="border-top:none;border-left:none">Scott</td> <td class="xl65" style="border-top:none;border-left:none">4</td> <td class="xl65" style="border-top:none;border-left:none">NS 4</td> <td class="xl65" style="border-top:none;border-left:none">NS 4</td> <td class="xl65" style="border-top:none;border-left:none">Engineering</td> <td class="xl65" style="border-top:none;border-left:none">6</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 7</td> <td class="xl76" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">2</td> <td class="xl74" style="border-top:none;border-left:none">Tim</td> <td class="xl74" style="border-top:none;border-left:none">1</td> <td class="xl74" style="border-top:none;border-left:none">NC 1</td> <td class="xl74" style="border-top:none;border-left:none">NC 1</td> <td class="xl74" style="border-top:none;border-left:none">Finance</td> <td class="xl74" style="border-top:none;border-left:none">12</td> </tr> </tbody></table>

3. Dataset – This worksheet is reserved for testing the Macro


<table style="width: 982px; height: 416px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2787;width:59pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:2759;width:58pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:1507;width:32pt" width="42"> <col style="mso-width-source:userset;mso-width-alt:1223;width:26pt" width="34"> <col style="mso-width-source:userset;mso-width-alt:1792;width:38pt" width="50"> <col style="mso-width-source:userset;mso-width-alt:2190;width:46pt" width="62"> <col style="mso-width-source:userset;mso-width-alt:3982;width:84pt" width="112"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:14.4pt" height="19"> <td class="xl70" style="height:14.4pt;width:48pt" width="64" height="19">
</td> <td style="width:59pt" width="78">Column A</td> <td class="xl73" style="width:58pt" width="78">Column B</td> <td style="width:32pt" width="42">Column C</td> <td style="width:26pt" width="34">Column D</td> <td style="width:38pt" width="50">Column E</td> <td style="width:46pt" width="62">Column F</td> <td style="width:84pt" width="112">Column G</td> <td style="width:48pt" width="64">Column H</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 1</td> <td class="xl71">Report Date</td> <td class="xl66" style="border-left:none">EmployeeID</td> <td class="xl72" style="border-left:none">Name</td> <td class="xl66" style="border-left:none">BW</td> <td class="xl66" style="border-left:none">Class</td> <td class="xl66" style="border-left:none">Make</td> <td class="xl66" style="border-left:none">Function</td> <td class="xl67" style="border-left:none">Hours</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 2</td> <td class="xl74" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">4</td> <td class="xl65" style="border-top:none;border-left:none">Ben</td> <td class="xl65" style="border-top:none;border-left:none">3</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">Quality Assurance</td> <td class="xl65" style="border-top:none;border-left:none">9</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 3</td> <td class="xl74" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">3</td> <td class="xl65" style="border-top:none;border-left:none">Jen</td> <td class="xl65" style="border-top:none;border-left:none">5</td> <td class="xl65" style="border-top:none;border-left:none">NS 7</td> <td class="xl65" style="border-top:none;border-left:none">NS 7</td> <td class="xl65" style="border-top:none;border-left:none">Accounting</td> <td class="xl65" style="border-top:none;border-left:none">5</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 4</td> <td class="xl74" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none">Mike</td> <td class="xl65" style="border-top:none;border-left:none">3</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">Quality Assurance</td> <td class="xl65" style="border-top:none;border-left:none">2</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 5</td> <td class="xl74" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none">Mike</td> <td class="xl65" style="border-top:none;border-left:none">3</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">Quality Assurance</td> <td class="xl65" style="border-top:none;border-left:none">7</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 6</td> <td class="xl74" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none">Mike</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">Finance</td> <td class="xl65" style="border-top:none;border-left:none">1</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 7</td> <td class="xl74" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">5</td> <td class="xl65" style="border-top:none;border-left:none">Scott</td> <td class="xl65" style="border-top:none;border-left:none">4</td> <td class="xl65" style="border-top:none;border-left:none">NS 4</td> <td class="xl65" style="border-top:none;border-left:none">NS 4</td> <td class="xl65" style="border-top:none;border-left:none">Engineering</td> <td class="xl65" style="border-top:none;border-left:none">6</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 8</td> <td class="xl74" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">2</td> <td class="xl65" style="border-top:none;border-left:none">Tim</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">Finance</td> <td class="xl65" style="border-top:none;border-left:none">4</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 9</td> <td class="xl74" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">2</td> <td class="xl65" style="border-top:none;border-left:none">Tim</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">NC 1</td> <td class="xl65" style="border-top:none;border-left:none">Finance</td> <td class="xl65" style="border-top:none;border-left:none">8</td> </tr> </tbody></table>



I created the Before Macro and After Macro worksheets just to illustrate what happens to the data when the Macro is executed. In reality, this excel workbook will only contain 1 worksheet that will go by the name of “Dataset”


I WANT TO CREATE A MACRO THAT WILL SUM THE HOURS OF ALL THE RECORDS BY THE COLUMNS “EMPLOYEEID” AND “BW”.


Thanks,
BC
<!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:RelyOnVML/> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]--><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:DoNotPromoteQF/> <w:LidThemeOther>EN-US</w:LidThemeOther> <w:LidThemeAsian>X-NONE</w:LidThemeAsian> <w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:SplitPgBreakAndParaMark/> <w:DontVertAlignCellWithSp/> <w:DontBreakConstrainedForcedTables/> <w:DontVertAlignInTxbx/> <w:Word11KerningPairs/> <w:CachedColBalance/> </w:Compatibility> <m:mathPr> <m:mathFont m:val="Cambria Math"/> <m:brkBin m:val="before"/> <m:brkBinSub m:val="--"/> <m:smallFrac m:val="off"/> <m:dispDef/> <m:lMargin m:val="0"/> <m:rMargin m:val="0"/> <m:defJc m:val="centerGroup"/> <m:wrapIndent m:val="1440"/> <m:intLim m:val="subSup"/> <m:naryLim m:val="undOvr"/> </m:mathPr></w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true" DefSemiHidden="true" DefQFormat="false" DefPriority="99" LatentStyleCount="267"> <w:LsdException Locked="false" Priority="0" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Normal"/> <w:LsdException Locked="false" Priority="9" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="heading 1"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/> <w:LsdException Locked="false" Priority="39" Name="toc 1"/> <w:LsdException Locked="false" Priority="39" Name="toc 2"/> <w:LsdException Locked="false" Priority="39" Name="toc 3"/> <w:LsdException Locked="false" Priority="39" Name="toc 4"/> <w:LsdException Locked="false" Priority="39" Name="toc 5"/> <w:LsdException Locked="false" Priority="39" Name="toc 6"/> <w:LsdException Locked="false" Priority="39" Name="toc 7"/> <w:LsdException Locked="false" Priority="39" Name="toc 8"/> <w:LsdException Locked="false" Priority="39" Name="toc 9"/> <w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/> <w:LsdException Locked="false" Priority="10" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Title"/> <w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/> <w:LsdException Locked="false" Priority="11" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/> <w:LsdException Locked="false" Priority="22" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Strong"/> <w:LsdException Locked="false" Priority="20" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/> <w:LsdException Locked="false" Priority="59" SemiHidden="false" UnhideWhenUsed="false" Name="Table Grid"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/> <w:LsdException Locked="false" Priority="1" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 1"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 1"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 1"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/> <w:LsdException Locked="false" Priority="34" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/> <w:LsdException Locked="false" Priority="29" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Quote"/> <w:LsdException Locked="false" Priority="30" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 1"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 1"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 2"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 2"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 2"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 2"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 2"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 3"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 3"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 3"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 3"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 3"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 4"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 4"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 4"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 4"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 4"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 5"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 5"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 5"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 5"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 5"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 6"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 6"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 6"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 6"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 6"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/> <w:LsdException Locked="false" Priority="19" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/> <w:LsdException Locked="false" Priority="21" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/> <w:LsdException Locked="false" Priority="31" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/> <w:LsdException Locked="false" Priority="32" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/> <w:LsdException Locked="false" Priority="33" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Book Title"/> <w:LsdException Locked="false" Priority="37" Name="Bibliography"/> <w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} </style> <![endif]-->

 

[mirabeau]

Try this on your test data as posted.

Sub sumz()
Dim a As Range, nr&, nc&
Dim d As Object, e, x, k&
Set a = Range("A1").CurrentRegion
nr = a.Rows.Count: nc = a.Columns.Count
Set d = CreateObject("scripting.dictionary")
d.comparemode = 1
For i = 1 To nr
    x = Empty
    For Each e In Range("B:H").Rows(i).Value
        x = x & Chr(30) & e
    Next
    If d(x) = Empty Then
        d(x) = a(i, "i")
    Else
        d(x) = d(x) + a(i, "i")
    End If
Next i
For Each e In d.keys
    k = k + 1
    Cells(k + 13, 1).Resize(, nc - 1) = Split(e, Chr(30), -1)
    Cells(k + 13, "i") = d(e)
Next
x = Columns("C:E"): Columns("C:E") = x
End Sub

 

[boldcode]

mirabeau,

I tested the macro and it does not work. It tells me that the "i" variable has not been defined. In addition, it somehow make a copy of the following data below right under it and I don't know why:

<table style="width: 1158px; height: 434px;" border="0" cellpadding="0" cellspacing="0"><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2787;width:59pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:2759;width:58pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:1507;width:32pt" width="42"> <col style="mso-width-source:userset;mso-width-alt:1223;width:26pt" width="34"> <col style="mso-width-source:userset;mso-width-alt:1792;width:38pt" width="50"> <col style="mso-width-source:userset;mso-width-alt:2190;width:46pt" width="62"> <col style="mso-width-source:userset;mso-width-alt:3982;width:84pt" width="112"> <col style="width:48pt" width="64"> <tbody><tr style="height:14.4pt" height="19"> <td class="xl68" style="height:14.4pt;width:48pt" width="64" height="19">
</td> <td style="width:59pt" width="78">Column A</td> <td class="xl71" style="width:58pt" width="78">Column B</td> <td style="width:32pt" width="42">Column C</td> <td style="width:26pt" width="34">Column D</td> <td style="width:38pt" width="50">Column E</td> <td style="width:46pt" width="62">Column F</td> <td style="width:84pt" width="112">Column G</td> <td style="width:48pt" width="64">Column H</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 1</td> <td class="xl69">Report Date</td> <td class="xl64" style="border-left:none">EmployeeID</td> <td class="xl70" style="border-left:none">Name</td> <td class="xl64" style="border-left:none">BW</td> <td class="xl64" style="border-left:none">Class</td> <td class="xl64" style="border-left:none">Make</td> <td class="xl64" style="border-left:none">Function</td> <td class="xl65" style="border-left:none">Hours</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 2</td> <td class="xl72" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">4</td> <td class="xl63" style="border-top:none;border-left:none">Ben</td> <td class="xl63" style="border-top:none;border-left:none">3</td> <td class="xl63" style="border-top:none;border-left:none">NC 9</td> <td class="xl63" style="border-top:none;border-left:none">NC 9</td> <td class="xl63" style="border-top:none;border-left:none">Quality Assurance</td> <td class="xl63" style="border-top:none;border-left:none">9</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 3</td> <td class="xl72" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">3</td> <td class="xl63" style="border-top:none;border-left:none">Jen</td> <td class="xl63" style="border-top:none;border-left:none">5</td> <td class="xl63" style="border-top:none;border-left:none">NS 7</td> <td class="xl63" style="border-top:none;border-left:none">NS 7</td> <td class="xl63" style="border-top:none;border-left:none">Accounting</td> <td class="xl63" style="border-top:none;border-left:none">5</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 4</td> <td class="xl72" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">1</td> <td class="xl63" style="border-top:none;border-left:none">Mike</td> <td class="xl63" style="border-top:none;border-left:none">3</td> <td class="xl63" style="border-top:none;border-left:none">NC 9</td> <td class="xl63" style="border-top:none;border-left:none">NC 9</td> <td class="xl63" style="border-top:none;border-left:none">Quality Assurance</td> <td class="xl63" style="border-top:none;border-left:none">2</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 5</td> <td class="xl72" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">1</td> <td class="xl63" style="border-top:none;border-left:none">Mike</td> <td class="xl63" style="border-top:none;border-left:none">3</td> <td class="xl63" style="border-top:none;border-left:none">NC 9</td> <td class="xl63" style="border-top:none;border-left:none">NC 9</td> <td class="xl63" style="border-top:none;border-left:none">Quality Assurance</td> <td class="xl63" style="border-top:none;border-left:none">7</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 6</td> <td class="xl72" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">1</td> <td class="xl63" style="border-top:none;border-left:none">Mike</td> <td class="xl63" style="border-top:none;border-left:none">1</td> <td class="xl63" style="border-top:none;border-left:none">NC 1</td> <td class="xl63" style="border-top:none;border-left:none">NC 1</td> <td class="xl63" style="border-top:none;border-left:none">Finance</td> <td class="xl63" style="border-top:none;border-left:none">1</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 7</td> <td class="xl72" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">5</td> <td class="xl63" style="border-top:none;border-left:none">Scott</td> <td class="xl63" style="border-top:none;border-left:none">4</td> <td class="xl63" style="border-top:none;border-left:none">NS 4</td> <td class="xl63" style="border-top:none;border-left:none">NS 4</td> <td class="xl63" style="border-top:none;border-left:none">Engineering</td> <td class="xl63" style="border-top:none;border-left:none">6</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 8</td> <td class="xl72" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">2</td> <td class="xl63" style="border-top:none;border-left:none">Tim</td> <td class="xl63" style="border-top:none;border-left:none">1</td> <td class="xl63" style="border-top:none;border-left:none">NC 1</td> <td class="xl63" style="border-top:none;border-left:none">NC 1</td> <td class="xl63" style="border-top:none;border-left:none">Finance</td> <td class="xl63" style="border-top:none;border-left:none">4</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 9</td> <td class="xl72" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">2</td> <td class="xl63" style="border-top:none;border-left:none">Tim</td> <td class="xl63" style="border-top:none;border-left:none">1</td> <td class="xl63" style="border-top:none;border-left:none">NC 1</td> <td class="xl63" style="border-top:none;border-left:none">NC 1</td> <td class="xl63" style="border-top:none;border-left:none">Finance</td> <td class="xl63" style="border-top:none;border-left:none">8</td> </tr> </tbody></table>
Also, how can these Macro accommodate more than 9 records? The reason I ask is because in the real data sets that I will be using it will contain more than a 1,000 records that need to be summarized.

Thanks,

BC

 

[mirabeau]

mirabeau,

I tested the macro and it does not work. It tells me that the "i" variable has not been defined. In addition, it somehow make a copy of the following data below right under it and I don't know why:

Also, how can these Macro accommodate more than 9 records? The reason I ask is because in the real data sets that I will be using it will contain more than a 1,000 records that need to be summarized.

Thanks,

BC

You are presumably using Option Explicit at the top of your code, which does then require all variables to be declared. Put in a new line
Dim i as long between some of the other Dim statements at top of the code.

How do you want the results displayed? Would this be on another worksheet, would it be replacing the original code, would it be somewhere else on the same worksheet, or ... ?

Yes, the macro can easily handle more than 9 records, it can handle many thousands.

Your title mentions 2 criteria. What are these 2 criteria? I had to guess and possibly I guessed wrong.

You say the macro didn't work. I tried it on your posted data and it did work.
Could you be more specific as to the way(s) in which it "does not work".

 

[boldcode]

mirabeau,

thanks first of all for your help.

First, I am using Option Explicit and I did put in a new line for "i" just like you told me

Second, I want the results displayed in a separate worksheet

Third, the 2 criteria are column B (the column title is "EmployeeID" found in row 1) and column D (the column title is "BW" found in row 1)

So let me give you an example of what the data will look like before the Macro and after the Macro summed up the "Hours" based on "EmployeeID" and "BW"

Data Before the Macro:

<table style="width: 1153px; height: 414px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:1934;width:41pt" width="54"> <col style="mso-width-source:userset;mso-width-alt:2787;width:59pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:2759;width:58pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:1450;width:31pt" width="41"> <col style="mso-width-source:userset;mso-width-alt:2275;width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2218;width:47pt" width="62"> <col style="mso-width-source:userset;mso-width-alt:2190;width:46pt" width="62"> <col style="mso-width-source:userset;mso-width-alt:3982;width:84pt" width="112"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:14.4pt" height="19"> <td class="xl68" style="height:14.4pt;width:41pt" width="54" height="19">
</td> <td style="width:59pt" width="78">Column A</td> <td class="xl73" style="width:58pt" width="78">Column B</td> <td style="width:31pt" width="41">Column C</td> <td style="width:48pt" width="64">Column D</td> <td style="width:47pt" width="62">Column E</td> <td style="width:46pt" width="62">Column F</td> <td style="width:84pt" width="112">Column G</td> <td style="width:48pt" width="64">Column H</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 1</td> <td class="xl69">Report Date</td> <td class="xl64" style="border-left:none">EmployeeID</td> <td class="xl70" style="border-left:none">Name</td> <td class="xl64" style="border-left:none">BW</td> <td class="xl64" style="border-left:none">Class</td> <td class="xl64" style="border-left:none">Make</td> <td class="xl64" style="border-left:none">Function</td> <td class="xl65" style="border-left:none">Hours</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 2</td> <td class="xl74" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">4</td> <td class="xl63" style="border-top:none;border-left:none">Ben</td> <td class="xl63" style="border-top:none;border-left:none">3</td> <td class="xl63" style="border-top:none;border-left:none">NC 9</td> <td class="xl63" style="border-top:none;border-left:none">NC 9</td> <td class="xl63" style="border-top:none;border-left:none">Quality Assurance</td> <td class="xl63" style="border-top:none;border-left:none">9</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 3</td> <td class="xl74" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">3</td> <td class="xl63" style="border-top:none;border-left:none">Jen</td> <td class="xl63" style="border-top:none;border-left:none">5</td> <td class="xl63" style="border-top:none;border-left:none">NS 7</td> <td class="xl63" style="border-top:none;border-left:none">NS 7</td> <td class="xl63" style="border-top:none;border-left:none">Accounting</td> <td class="xl63" style="border-top:none;border-left:none">5</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 4</td> <td class="xl74" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">1</td> <td class="xl71" style="border-top:none;border-left:none">Mike</td> <td class="xl71" style="border-top:none;border-left:none">3</td> <td class="xl71" style="border-top:none;border-left:none">NC 9</td> <td class="xl71" style="border-top:none;border-left:none">NC 9</td> <td class="xl71" style="border-top:none;border-left:none">Quality Assurance</td> <td class="xl71" style="border-top:none;border-left:none">2</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 5</td> <td class="xl74" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">1</td> <td class="xl71" style="border-top:none;border-left:none">Mike</td> <td class="xl71" style="border-top:none;border-left:none">3</td> <td class="xl71" style="border-top:none;border-left:none">NC 9</td> <td class="xl71" style="border-top:none;border-left:none">NC 9</td> <td class="xl71" style="border-top:none;border-left:none">Quality Assurance</td> <td class="xl71" style="border-top:none;border-left:none">7</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 6</td> <td class="xl74" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">1</td> <td class="xl71" style="border-top:none;border-left:none">Mike</td> <td class="xl71" style="border-top:none;border-left:none">1</td> <td class="xl71" style="border-top:none;border-left:none">NC 1</td> <td class="xl71" style="border-top:none;border-left:none">NC 1</td> <td class="xl71" style="border-top:none;border-left:none">Finance</td> <td class="xl71" style="border-top:none;border-left:none">1</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 7</td> <td class="xl74" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">5</td> <td class="xl63" style="border-top:none;border-left:none">Scott</td> <td class="xl63" style="border-top:none;border-left:none">4</td> <td class="xl63" style="border-top:none;border-left:none">NS 4</td> <td class="xl63" style="border-top:none;border-left:none">NS 4</td> <td class="xl63" style="border-top:none;border-left:none">Engineering</td> <td class="xl63" style="border-top:none;border-left:none">6</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 8</td> <td class="xl74" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">2</td> <td class="xl72" style="border-top:none;border-left:none">Tim</td> <td class="xl72" style="border-top:none;border-left:none">1</td> <td class="xl72" style="border-top:none;border-left:none">NC 1</td> <td class="xl72" style="border-top:none;border-left:none">NC 1</td> <td class="xl72" style="border-top:none;border-left:none">Finance</td> <td class="xl72" style="border-top:none;border-left:none">4</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 9</td> <td class="xl74" style="border-top:none">6/9/2011</td> <td class="xl63" style="border-top:none;border-left:none">2</td> <td class="xl72" style="border-top:none;border-left:none">Tim</td> <td class="xl72" style="border-top:none;border-left:none">1</td> <td class="xl72" style="border-top:none;border-left:none">NC 1</td> <td class="xl72" style="border-top:none;border-left:none">NC 1</td> <td class="xl72" style="border-top:none;border-left:none">Finance</td> <td class="xl72" style="border-top:none;border-left:none">8</td> </tr> </tbody></table>
What Results will look like after the Macro has been executed:

<table style="width: 1073px; height: 336px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2787;width:59pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:2759;width:58pt" width="78"> <col style="mso-width-source:userset;mso-width-alt:1507;width:32pt" width="42"> <col style="mso-width-source:userset;mso-width-alt:1223;width:26pt" width="34"> <col style="mso-width-source:userset;mso-width-alt:1792;width:38pt" width="50"> <col style="mso-width-source:userset;mso-width-alt:2190;width:46pt" width="62"> <col style="mso-width-source:userset;mso-width-alt:3982;width:84pt" width="112"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:14.4pt" height="19"> <td class="xl70" style="height:14.4pt;width:48pt" width="64" height="19">
</td> <td style="width:59pt" width="78">Column A</td> <td class="xl75" style="width:58pt" width="78">Column B</td> <td style="width:32pt" width="42">Column C</td> <td style="width:26pt" width="34">Column D</td> <td style="width:38pt" width="50">Column E</td> <td style="width:46pt" width="62">Column F</td> <td style="width:84pt" width="112">Column G</td> <td style="width:48pt" width="64">Column H</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 1</td> <td class="xl71">Report Date</td> <td class="xl66" style="border-left:none">EmployeeID</td> <td class="xl72" style="border-left:none">Name</td> <td class="xl66" style="border-left:none">BW</td> <td class="xl66" style="border-left:none">Class</td> <td class="xl66" style="border-left:none">Make</td> <td class="xl66" style="border-left:none">Function</td> <td class="xl67" style="border-left:none">Hours</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 2</td> <td class="xl76" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">4</td> <td class="xl65" style="border-top:none;border-left:none">Ben</td> <td class="xl65" style="border-top:none;border-left:none">3</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">NC 9</td> <td class="xl65" style="border-top:none;border-left:none">Quality Assurance</td> <td class="xl65" style="border-top:none;border-left:none">9</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 3</td> <td class="xl76" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">3</td> <td class="xl65" style="border-top:none;border-left:none">Jen</td> <td class="xl65" style="border-top:none;border-left:none">5</td> <td class="xl65" style="border-top:none;border-left:none">NS 7</td> <td class="xl65" style="border-top:none;border-left:none">NS 7</td> <td class="xl65" style="border-top:none;border-left:none">Accounting</td> <td class="xl65" style="border-top:none;border-left:none">5</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 4</td> <td class="xl76" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl73" style="border-top:none;border-left:none">Mike</td> <td class="xl73" style="border-top:none;border-left:none">3</td> <td class="xl73" style="border-top:none;border-left:none">NC 9</td> <td class="xl73" style="border-top:none;border-left:none">NC 9</td> <td class="xl73" style="border-top:none;border-left:none">Quality Assurance</td> <td class="xl73" style="border-top:none;border-left:none">9</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 5</td> <td class="xl76" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">1</td> <td class="xl73" style="border-top:none;border-left:none">Mike</td> <td class="xl73" style="border-top:none;border-left:none">1</td> <td class="xl73" style="border-top:none;border-left:none">NC 1</td> <td class="xl73" style="border-top:none;border-left:none">NC 1</td> <td class="xl73" style="border-top:none;border-left:none">Finance</td> <td class="xl73" style="border-top:none;border-left:none">1</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 6</td> <td class="xl76" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">5</td> <td class="xl65" style="border-top:none;border-left:none">Scott</td> <td class="xl65" style="border-top:none;border-left:none">4</td> <td class="xl65" style="border-top:none;border-left:none">NS 4</td> <td class="xl65" style="border-top:none;border-left:none">NS 4</td> <td class="xl65" style="border-top:none;border-left:none">Engineering</td> <td class="xl65" style="border-top:none;border-left:none">6</td> </tr> <tr style="height:14.4pt" height="19"> <td style="height:14.4pt" height="19">Row 7</td> <td class="xl76" style="border-top:none">6/9/2011</td> <td class="xl65" style="border-top:none;border-left:none">2</td> <td class="xl74" style="border-top:none;border-left:none">Tim</td> <td class="xl74" style="border-top:none;border-left:none">1</td> <td class="xl74" style="border-top:none;border-left:none">NC 1</td> <td class="xl74" style="border-top:none;border-left:none">NC 1</td> <td class="xl74" style="border-top:none;border-left:none">Finance</td> <td class="xl74" style="border-top:none;border-left:none">12</td> </tr> </tbody></table>
let me know if this makes sense or not

 

[mirabeau]

OK. Your feedback helped a lot.

Try this code

Sub sum_with_2_criteria()
'DATA START FROM CELL(A1) IN SHEET1
'RESULTS START FROM CELL(A1) ON SHEET2

Dim a, nr&, nc&, i&
Dim d As Object, x, k&
Sheets("Sheet1").Activate
a = Range("A1").CurrentRegion
nr = UBound(a, 1): nc = UBound(a, 2)
ReDim c(1 To nr, 1 To nc)
Set d = CreateObject("scripting.dictionary")
d.comparemode = 1

For i = 1 To nr
    x = a(i, 2) & Chr(30) & a(i, 4)
    If d(x) = Empty Then
        k = k + 1
        d(x) = k
        For j = 1 To nc
            c(k, j) = a(i, j)
        Next j
    Else
        c(d(x), 8) = c(d(x), 8) + a(i, 8)
    End If
Next i

Sheets("Sheet2").Range("A1").Resize(k, nc) = c
Sheets("Sheet2").Activate
End Sub

 

[boldcode]

mirabeau,

The code works fine now, but how can I get to displays results in worksheet that it creates for me as oppose to me creating a worksheet named "Sheet2" everytime.

 

[boldcode]

mirabeau,

In addition to my last post how can I swap this piece of the code to just point towards the Activate Sheet regardless of the name of the worksheet

Sheets("Sheet1").Activate</pre>

 

[boldcode]

mirabeau,

No worries, I figured it out. Thank you for getting the code to sum up properly though. It works awesome thanks to you!!!ray:

 

[mirabeau]

boldcode,

Thanks for the feedback. That's always useful to get. Good that it seems to work well for you.

If you want to add another sheet to display results on, you can modify the lines relating to Sheet2 near the bottom to:

With Sheets.Add
    .Name = "a_new_sheet"   'or named whatever else you want
    .Range("A1").Resize(k, nc) = c
End With

but to do it this way you should get the code to check first there isn't already a sheet called a_new_sheet otherwise you'd likely get an error message.