Combine & sum rows of data

[Cerberus]

New Member
Joined
Nov 30, 2009
Messages
2
Hello,

First off, I want to apoligize for adding the same topic to an already popular subject. I have been searching these forums for a while and have noticed this is a popular question to be asked. I have found some solutions that were close but none that seemed to work just right, or in the way I need it done.

I have attached a sample table of the data set I have, it consists of 4 columns that contain a part number, description, quantity, and cost.

In this list there are duplicate part numbers, and I would like to combine them into 1 instance of each unique part number and sum the quanties and costs, then delete the extra rows. I would like to accomplish this in a VBA code, because if it works I would like to implement it into another tool I have created that merges two sheets, but does not combine duplicates.

Thank you for any and all help you can provide.


<TABLE style="WIDTH: 243pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=324 border=0 x:str><COLGROUP><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=96 height=17>Part No</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 75pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=100>Description</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>Qty</TD><TD class=xl22 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64>Cost</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=96 height=17 x:num>1018058</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 75pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=100>Part 1</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>202</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="37.975999999999999">37.976</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=96 height=17 x:num>1018058</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 75pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=100>Part 1</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>192</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="36.096000000000004">36.096</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=96 height=17 x:num>1018058</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 75pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=100>Part 1</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>192</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="36.096000000000004">36.096</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=96 height=17 x:num>1018058</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 75pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=100>Part 1</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>204</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="38.351999999999997">38.352</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=96 height=17 x:num>1007346</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 75pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=100>Part 3</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>48</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=96 height=17 x:num>2018916</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 75pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=100>Part 4</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>20</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=96 height=17 x:num>2018916</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 75pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=100>Part 4</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>116</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=96 height=17 x:num>2018916</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 75pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=100>Part 4</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>128</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=96 height=17 x:num>2100497</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 75pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=100>Part 5</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>2</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>46.28</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=96 height=17 x:num>2100497</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 75pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=100>Part 5</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>9</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>208.26</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=96 height=17 x:num>2100497</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 75pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=100>Part 5</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>16</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>370.24</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=96 height=17 x:num>2087217</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 75pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=100>Part 6</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>11</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>1485</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=96 height=17 x:num>2087217</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 75pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=100>Part 6</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>11</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>1485</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=96 height=17 x:num>2087217</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 75pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=100>Part 6</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>12</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>1620</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=96 height=17 x:num>2087217</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 75pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=100>Part 6</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>10</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>1350</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=96 height=17 x:num>2026268</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 75pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=100>Part 7</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>4</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="2.0828000000000002">2.0828</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=96 height=17>M010449</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 75pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=100>Part 8</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>10</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="101.45899999999999">101.459</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=96 height=17>M010449</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 75pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=100>Part 8</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>16</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="162.33439999999999">162.3344</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=96 height=17 x:num>2094686</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 75pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=100>Part 9</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num>231</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: white" width=64 x:num="303.74189999999999">303.7419</TD></TR></TBODY></TABLE>
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
[Cerberus],

Welcome to the MrExcel board.

Sample data in worksheet "Sheet1", before and after the macro:


Excel Workbook
ABCD
1Part NoDescriptionQtyCost
21018058Part 120237.976
31018058Part 119236.096
41018058Part 119236.096
51018058Part 120438.352
61007346Part 3480
72018916Part 4200
82018916Part 41160
92018916Part 41280
102100497Part 5246.28
112100497Part 59208.26
122100497Part 516370.24
132087217Part 6111485
142087217Part 6111485
152087217Part 6121620
162087217Part 6101350
172026268Part 742.0828
18M010449Part 810101.459
19M010449Part 816162.3344
202094686Part 9231303.7419
21
Sheet1





The macro will create worksheet "Summary":


Excel Workbook
ABCD
1Part NoDescriptionQtyCost
21018058Part 1790148.52
31007346Part 3480
42018916Part 42640
52100497Part 527624.78
62087217Part 6445940
72026268Part 742.0828
8M010449Part 826263.7934
92094686Part 9231303.7419
10
Summary





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code:
Option Explicit
Sub CreateSummary()
' hiker95, 11/30/2009
Dim LR As Long, LR2 As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Application.ScreenUpdating = False
Sheets("Sheet1").Select
Set ws1 = Sheets("Sheet1")
With ws1
  LR = .Cells(Rows.Count, 1).End(xlUp).Row
  .Range("A1:B" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("F1"), Unique:=True
  .Range("H1").Resize(, 2).Value = [{"Qty","Cost"}]
  .Range("H1:I1").Font.Bold = True
  LR2 = .Cells(Rows.Count, 6).End(xlUp).Row
  With .Range("H2:H" & LR2)
    .FormulaR1C1 = "=SUMPRODUCT(--(R2C1:R" & LR & "C1=RC6),--(R2C2:R" & LR & "C2=RC7),R2C3:R" & LR & "C3)"
    .Value = .Value
  End With
  With .Range("I2:I" & LR2)
    .FormulaR1C1 = "=SUMPRODUCT(--(R2C1:R" & LR & "C1=RC6),--(R2C2:R" & LR & "C2=RC7),R2C4:R" & LR & "C4)"
    .Value = .Value
  End With
  On Error Resume Next
  Sheets("Summary").Select
  If Err Then Worksheets.Add(After:=ws1).Name = "Summary"
  On Error GoTo 0
  Set ws2 = Sheets("Summary")
  ws2.Cells.ClearContents
  With ws1.Range("F1:I" & LR2)
    .Copy ws2.Range("A1")
    .ClearContents
  End With
End With
ws2.Columns.AutoFit
Application.ScreenUpdating = True
End Sub


Then run the "CreateSummary" macro.
 

[Cerberus]

New Member
Joined
Nov 30, 2009
Messages
2
Thank you for such a quick response I appreciate it. Thanks for putting a little more time into this, most of the VBA code I have found online does not have error trapping.

Could you walk through it a bit?, I am trying to understand what is going on, to learn and modify as needed for other projects.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
[Cerberus],

Could you walk through it a bit?, I am trying to understand what is going on, to learn and modify as needed for other projects.


Code:
  'Option Explicit requires me to define my variables
  '
Option Explicit
Sub CreateSummary()
' hiker95, 11/30/2009

  'My variables are defined in the next two lines of code.
  '
Dim LR As Long, LR2 As Long
Dim ws1 As Worksheet, ws2 As Worksheet

  'Turn off screen updatting to stop screen flicker
  '
Application.ScreenUpdating = False

  'Select/make active "Sheet1"
  '
Sheets("Sheet1").Select

  'Set variable 'ws1' as Sheets("Sheet1")
  '
Set ws1 = Sheets("Sheet1")
With ws1
  
    'Find the last used row in column 1 = "A"
    '
  LR = .Cells(Rows.Count, 1).End(xlUp).Row
  
    'Use AdvancedFilter on range A1:B LR
    '  of range A1:V20
    '
    'And, copy the unique values to F1
    '
  .Range("A1:B" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("F1"), Unique:=True
  
    'Put titles in H1 and I1
    '
  .Range("H1").Resize(, 2).Value = [{"Qty","Cost"}]
  
    'And, make the titles bold
    '
  .Range("H1:I1").Font.Bold = True
  
    'Find the last row of column F = 6
    '
  LR2 = .Cells(Rows.Count, 6).End(xlUp).Row
  
    'I find if easier to use R1C1 reference for filling a range with a formula
    '
  With .Range("H2:H" & LR2)
    
      'The next .FormulaR1C1 translates to:
      '             =SUMPRODUCT(--($A$2:$A$20=$F2),--($B$2:$B$20=$G2),$C$2:$C$20)
      '
    .FormulaR1C1 = "=SUMPRODUCT(--(R2C1:R" & LR & "C1=RC6),--(R2C2:R" & LR & "C2=RC7),R2C3:R" & LR & "C3)"
    
      'Change the formula to its value
      '
    .Value = .Value
  End With
  With .Range("I2:I" & LR2)
  
      'The next .FormulaR1C1 translates to:
      '             =SUMPRODUCT(--($A$2:$A$20=$F2),--($B$2:$B$20=$G2),$D$2:$D$20)
      '
    .FormulaR1C1 = "=SUMPRODUCT(--(R2C1:R" & LR & "C1=RC6),--(R2C2:R" & LR & "C2=RC7),R2C4:R" & LR & "C4)"
    
      'Change the formula to its value
      '
    .Value = .Value
  End With
  
    'Setup an error routine in case worksheet "Summary" does not exist
    '
  On Error Resume Next
  Sheets("Summary").Select
  If Err Then Worksheets.Add(After:=ws1).Name = "Summary"
  On Error GoTo 0
  
    'Set variable 'ws2' as Sheets("Summary")
    '
  Set ws2 = Sheets("Summary")
  
    'Clear all cells in Sheets("Summary")
    '
  ws2.Cells.ClearContents
  
    'Copy ws1.Range("F1:I9") to ws2.Range("A1")
    '
  With ws1.Range("F1:I" & LR2)
    .Copy ws2.Range("A1")
    
      'Then clear ws1.Range("F1:I9")
      '
    .ClearContents
  End With
End With

  'AutoFit the width of ws2 columns
  '
ws2.Columns.AutoFit

  'Turn on screen updatting, and exit/finish the macro
  '
Application.ScreenUpdating = True
End Sub





Training / Books / Sites

How to Learn to Write Macros
http://articles.excelyogi.com/playin...ba/2008/10/27/

How to use the macro recorder
http://articles.excelyogi.com/

Click here and scroll down to Getting Started with VBA.
http://www.datapigtechnologies.com/ExcelMain.htm

If you are serious about learning VBA try
http://www.add-ins.com/vbhelp.htm

Excel Tutorials and Tips - VBA - macros - training
http://www.mrexcel.com/articles.shtml

See David McRitchie's site if you just started with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

http://www.excel-vba.com/
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.exceltip.com/excel_links.html

(livelessons video)
Excel VBA and Macros with MrExcel
ISBN: 0-7897-3938-0
http://www.amazon.com/Excel-Macros-M...7936479&sr=1-1

http://www.xl-central.com/index.html

Excel 2003 Power Programming with VBA, by John Walkenbach
VBA and Macros for Microsoft Excel, by Bill Jelen "Mr.Excel" and Tracy Syrstad
Excel Hacks 100 Industrial-Strength Tips & Tools, by David & Traina Hawley
VBA and Macros for Microsoft Excel 2007, by Bill Jelen "Mr.Excel" and Tracy Syrstad

Excel 2007 Book: you can try this...there is a try before you buy ebook available at this link…
http://www.mrexcel.com/learnexcel2.shtml
 

RAYLWARD102

Well-known Member
Joined
May 27, 2010
Messages
524

ADVERTISEMENT

I've never used R1C1 reference. How can I use this code with regular refrences. I'd like to do something simlar to the user who originally posted but need to modify some of the r1c1 refrences. Just don't know how to.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
RAYLWARD102,

In the future you should start your own new post, and in the new post put a link to the original post by [Cerberus].


Sample data in Sheet1 before the macro:


Excel Workbook
ABCD
1Part NoDescriptionQtyCost
21018058Part 120237.976
31018058Part 119236.096
41018058Part 119236.096
51018058Part 120438.352
61007346Part 3480
72018916Part 4200
82018916Part 41160
92018916Part 41280
102100497Part 5246.28
112100497Part 59208.26
122100497Part 516370.24
132087217Part 6111485
142087217Part 6111485
152087217Part 6121620
162087217Part 6101350
172026268Part 742.0828
18M010449Part 810101.459
19M010449Part 816162.3344
202094686Part 9231303.7419
21
Sheet1





After the macro in a new worksheet Summary:


Excel Workbook
ABCD
1Part NoDescriptionQtyCost
21018058Part 1790148.52
31007346Part 3480
42018916Part 42640
52100497Part 527624.78
62087217Part 6445940
72026268Part 742.0828
8M010449Part 826263.7934
92094686Part 9231303.7419
10
Summary





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code:
Option Explicit
Sub CreateSummaryV2()
' hiker95, 09/01/2010, ME432975
Dim LR As Long, LR2 As Long
Dim w1 As Worksheet, wS As Worksheet
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
If Not Evaluate("ISREF(Summary!A1)") Then Worksheets.Add(After:=w1).Name = "Summary"
Set wS = Worksheets("Summary")
wS.UsedRange.Clear
LR = w1.Cells(Rows.Count, 1).End(xlUp).Row
w1.Range("A1:B" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wS.Range("A1"), Unique:=True
wS.Range("C1").Resize(, 2).Value = [{"Qty","Cost"}]
wS.Range("C1:D1").Font.Bold = True
LR2 = wS.Cells(Rows.Count, 1).End(xlUp).Row
wS.Range("C2").Formula = "=SUMPRODUCT(--(" & w1.Name & "!$A$2:$A$" & LR & "=$A2),--(" & w1.Name & "!$B$2:$B$" & LR & "=$B2)," & w1.Name & "!$C$2:$C$" & LR & ")"
wS.Range("C2").Copy wS.Range("C3:C" & LR2)
wS.Range("D2").Formula = "=SUMPRODUCT(--(" & w1.Name & "!$A$2:$A$" & LR & "=$A2),--(" & w1.Name & "!$B$2:$B$" & LR & "=$B2)," & w1.Name & "!$D$2:$D$" & LR & ")"
wS.Range("D2").Copy wS.Range("D3:D" & LR2)
wS.UsedRange.Columns.AutoFit
wS.Activate
Application.ScreenUpdating = True
End Sub


Then run the new "CreateSummaryV2" macro.


If you do not want to see the SUMPRODUCT formulae on worksheet Summary I can update the code
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649

ADVERTISEMENT

RAYLWARD102,


If you do not want to see the SUMPRODUCT formulae on worksheet Summary I can update the code.
 

RAYLWARD102

Well-known Member
Joined
May 27, 2010
Messages
524
Ok... I've been playing with this for some time now. I've played with the code a bit and was trying to manipulate it for handling more columns of data.
I can successfully get it to find sort unique records from 5 columns but cannot seem to get it to sum the 4 columns after the unique sort. I'm able to sum the last column. Anyone have an idea what I'm doing wrong here?
Here is the modified code.

Code:
'hiker 95
Dim LR As Long, LR2 As Long
Dim w1 As Worksheet, wS As Worksheet
Application.ScreenUpdating = False
Set w1 = Worksheets("dump2")
If Not Evaluate("ISREF(org!A1)") Then Worksheets.Add(After:=w1).Name = "org"
Set wS = Worksheets("org")
wS.UsedRange.Clear
LR = w1.Cells(Rows.Count, 1).End(xlUp).Row
w1.Range("A1:e" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wS.Range("A1"), Unique:=True
LR2 = wS.Cells(Rows.Count, 1).End(xlUp).Row
wS.Range("f1").Formula = "=SUMPRODUCT(--(" & w1.Name & "!$A$1:$A$" & LR & "=$A1),--(" & w1.Name & "!$B$1:$B$" & LR & "=$B1)," & w1.Name & "!$f$1:$f$" & LR & ")"
wS.Range("f1").Copy wS.Range("f1:f" & LR2)
wS.Range("g1").Formula = "=SUMPRODUCT(--(" & w1.Name & "!$A$1:$A$" & LR & "=$A1),--(" & w1.Name & "!$B$1:$B$" & LR & "=$B1)," & w1.Name & "!$g$1:$g$" & LR & ")"
wS.Range("g1").Copy wS.Range("g1:g" & LR2)
wS.Range("h1").Formula = "=SUMPRODUCT(--(" & w1.Name & "!$A$1:$A$" & LR & "=$A1),--(" & w1.Name & "!$B$1:$B$" & LR & "=$B1)," & w1.Name & "!$h$1:$h$" & LR & ")"
wS.Range("h1").Copy wS.Range("h1:h" & LR2)
wS.Range("i1").Formula = "=SUMPRODUCT(--(" & w1.Name & "!$A$1:$A$" & LR & "=$A1),--(" & w1.Name & "!$B$1:$B$" & LR & "=$B1)," & w1.Name & "!$i$1:$i$" & LR & ")"
wS.Range("i1").Copy wS.Range("i1:f" & LR2)
wS.UsedRange.Columns.AutoFit
wS.Activate
Application.ScreenUpdating = True

The data looks like:

Code:
Mike Smith    Baltimore    MD    21211    UNITED STATES    1    2.5    1    1
Mike Smith    Baltimore    MD    21211    UNITED STATES    1    3.0    1    2

and should sort / sum to 

Mike Smith    Baltimore    MD    21211    UNITED STATES    2    5.5    2    3
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,651
Members
414,399
Latest member
Ninjee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top