Combine & sum rows of data

[Cerberus] · Nov 30, 2009

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>

hiker95 · Sep 17, 2010

RAYLWARD102,

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

Or, you can upload your workbook to www.box.net and provide us with a link to your workbook.

hiker95 · Sep 17, 2010

RAYLWARD102,

In the future I would suggest that you start your own post.

RAYLWARD102 · Sep 17, 2010

Excel Workbook

A

B

C

D

E

F

G

H

I

1

Mike Smith

Cumberland

BC

V0R 1S0

CANADA

1

0.5

1

7.5

2

Mike Smith

Cumberland

BC

V0R 1S0

CANADA

1

0.3

1

2.25

3

Mike Smith

Cumberland

BC

V0R 1S0

CANADA

1

0.3

1

2.25

4

Mike Smith

Cumberland

BC

V0R 1S0

CANADA

1

0.2

1

2.25

5

Mike Smith

Cumberland

BC

V0R 1S0

CANADA

1

0.4

4

2.25

6

Mike Smith

Cumberland

BC

V0R 1S0

CANADA

1

0.5

1

2.25

7

John Travolta

Lower Sackville

NS

B4C 3X3

CANADA

1

0.45

1

9.99

8

John Travolta

Lower Sackville

NS

B4C 3X3

CANADA

1

0.45

1

9.99

9

Leo Nard

Niagara Falls

ON

L2G 2R6

CANADA

1

0.55

1

9.99

10

Leo Nard

Niagara Falls

ON

L2G 2R6

CANADA

1

0.6

1

9.99

Sheet1

hiker95 · Sep 17, 2010

RAYLWARD102,

Can we have a screenshot of worksheet org that has been manually formatted.

RAYLWARD102 · Sep 17, 2010

Excel Workbook

A

B

C

D

E

F

G

H

I

1

Mike Smith

Cumberland

BC

V0R 1S0

CANADA

1

0.5

1

7.5

2

Mike Smith

Cumberland

BC

V0R 1S0

CANADA

1

0.3

1

2.25

3

Mike Smith

Cumberland

BC

V0R 1S0

CANADA

1

0.3

1

2.25

4

Mike Smith

Cumberland

BC

V0R 1S0

CANADA

1

0.2

1

2.25

5

Mike Smith

Cumberland

BC

V0R 1S0

CANADA

1

0.4

4

2.25

6

Mike Smith

Cumberland

BC

V0R 1S0

CANADA

1

0.5

1

2.25

7

John Travolta

Lower Sackville

NS

B4C 3X3

CANADA

1

0.45

1

9.99

8

John Travolta

Lower Sackville

NS

B4C 3X3

CANADA

1

0.45

1

9.99

9

Leo Nard

Niagara Falls

ON

L2G 2R6

CANADA

1

0.55

1

9.99

10

Leo Nard

Niagara Falls

ON

L2G 2R6

CANADA

1

0.6

1

9.99

Sheet1

RAYLWARD102 · Sep 17, 2010

here is the org sheet

Excel Workbook

A

B

C

D

E

F

G

H

I

14

Mike Smith

Cumberland

BC

V0R 1S0

CANADA

0

15

Mike Smith

Cumberland

BC

V0R 1S0

CANADA

0

16

John Travolta

Lower Sackville

NS

B4C 3X3

CANADA

0

17

Leo Nard

Niagara Falls

ON

L2G 2R6

CANADA

0

Sheet1

RAYLWARD102 · Sep 17, 2010

forgive me as I have never used the html genie.......Noob.

hiker95 · Sep 17, 2010

RAYLWARD102,

Sample data before the new macro:

Excel Workbook

A

B

C

D

E

F

G

H

I

1

Mike Smith

Cumberland

BC

V0R 1S0

CANADA

1

0.5

1

7.5

2

Mike Smith

Cumberland

BC

V0R 1S0

CANADA

1

0.3

1

2.25

3

Mike Smith

Cumberland

BC

V0R 1S0

CANADA

1

0.3

1

2.25

4

Mike Smith

Cumberland

BC

V0R 1S0

CANADA

1

0.2

1

2.25

5

Mike Smith

Cumberland

BC

V0R 1S0

CANADA

1

0.4

4

2.25

6

Mike Smith

Cumberland

BC

V0R 1S0

CANADA

1

0.5

1

2.25

7

John Travolta

Lower Sackville

NS

B4C 3X3

CANADA

1

0.45

1

9.99

8

John Travolta

Lower Sackville

NS

B4C 3X3

CANADA

1

0.45

1

9.99

9

Leo Nard

Niagara Falls

ON

L2G 2R6

CANADA

1

0.55

1

9.99

10

Leo Nard

Niagara Falls

ON

L2G 2R6

CANADA

1

0.6

1

9.99

11

dump2

After the macro:

Excel Workbook

A

B

C

D

E

F

G

H

I

1

Mike Smith

Cumberland

BC

V0R 1S0

CANADA

2

John Travolta

Lower Sackville

NS

B4C 3X3

CANADA

3

Leo Nard

Niagara Falls

ON

L2G 2R6

CANADA

4

org

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 CreateOrg()
' hiker95, 09/17/2010, ME432975
' For RAYLWARD102
Dim LR As Long, LR2 As Long
Dim w1 As Worksheet, wO As Worksheet
Application.ScreenUpdating = False
Set w1 = Worksheets(1)
w1.Columns("F:I").Copy
w1.Range("K1").PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks:=False, Transpose:=False
w1.Columns("K:N").Copy w1.Range("F1")
w1.Columns("K:N").ClearContents
Range("A1").Select
w1.Range("A1").EntireRow.Insert
w1.Range("A1").Resize(, 5).Value = [{"T1","T2","T3","T4","T5"}]
If Not Evaluate("ISREF(org!A1)") Then Worksheets.Add(After:=w1).Name = "org"
Set wO = Worksheets("org")
wO.UsedRange.Clear
LR = w1.Cells(Rows.Count, 1).End(xlUp).Row
w1.Range("A1:E" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wO.Range("A1"), Unique:=True
w1.Range("A1").EntireRow.Delete
LR = w1.Cells(Rows.Count, 1).End(xlUp).Row
wO.Range("A1").EntireRow.Delete
LR2 = wO.Cells(Rows.Count, 1).End(xlUp).Row
wO.Range("F1").Formula = "=SUMPRODUCT(--(" & w1.Name & "!$A$1:$A$" & LR & "=$A1),--(" & w1.Name & "!$B$1:$B$" & LR & "=$B1),--(" & w1.Name & "!$C$1:$C$" & LR & "=$C1),--(" & w1.Name & "!$D$1:$D$" & LR & "=$D1),--(" & w1.Name & "!$E$1:$E$" & LR & "=$E1)," & w1.Name & "!$F$1:$F$" & LR & ")"
wO.Range("F1").Copy wO.Range("F2:F" & LR2)
wO.Range("G1").Formula = "=SUMPRODUCT(--(" & w1.Name & "!$A$1:$A$" & LR & "=$A1),--(" & w1.Name & "!$B$1:$B$" & LR & "=$B1),--(" & w1.Name & "!$C$1:$C$" & LR & "=$C1),--(" & w1.Name & "!$D$1:$D$" & LR & "=$D1),--(" & w1.Name & "!$E$1:$E$" & LR & "=$E1)," & w1.Name & "!$G$1:$G$" & LR & ")"
wO.Range("G1").Copy wO.Range("G2:G" & LR2)
wO.Range("H1").Formula = "=SUMPRODUCT(--(" & w1.Name & "!$A$1:$A$" & LR & "=$A1),--(" & w1.Name & "!$B$1:$B$" & LR & "=$B1),--(" & w1.Name & "!$C$1:$C$" & LR & "=$C1),--(" & w1.Name & "!$D$1:$D$" & LR & "=$D1),--(" & w1.Name & "!$E$1:$E$" & LR & "=$E1)," & w1.Name & "!$H$1:$H$" & LR & ")"
wO.Range("H1").Copy wO.Range("H2:H" & LR2)
wO.Range("I1").Formula = "=SUMPRODUCT(--(" & w1.Name & "!$A$1:$A$" & LR & "=$A1),--(" & w1.Name & "!$B$1:$B$" & LR & "=$B1),--(" & w1.Name & "!$C$1:$C$" & LR & "=$C1),--(" & w1.Name & "!$D$1:$D$" & LR & "=$D1),--(" & w1.Name & "!$E$1:$E$" & LR & "=$E1)," & w1.Name & "!$I$1:$I$" & LR & ")"
wO.Range("I1").Copy wO.Range("I2:I" & LR2)
wO.UsedRange.Columns.AutoFit
wO.Activate
Application.ScreenUpdating = True
End Sub

Then run the "CreateOrg" macro.

RAYLWARD102 · Sep 18, 2010

Thanks! It works in 2003! not in 2007

what would cause that?

hiker95 · Sep 18, 2010

RAYLWARD102,

The CreateOrg macro works for me in Excel 2003 and 2007.

The macro works on the first worksheet in the workbook no matter what the sheet name is. Is your raw data sheet always named dump2? Do you want me to adjust the macro to work on the active worksheet in your workbook?

What is not working?

Are you receiving an error message?

Please upload your workbook to www.box.net and provide us with a link to your workbook.

Combine & sum rows of data

[Cerberus]

New Member

hiker95

Well-known Member

Excel Facts

hiker95

Well-known Member

RAYLWARD102

Well-known Member

hiker95

Well-known Member

RAYLWARD102

Well-known Member

RAYLWARD102

Well-known Member

RAYLWARD102

Well-known Member

hiker95

Well-known Member

RAYLWARD102

Well-known Member

hiker95

Well-known Member

Similar threads

Forum statistics

Share this page

Combine & sum rows of data

New Member

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock