# Combine & sum rows of data

This is a discussion on Combine & sum rows of data within the Excel Questions forums, part of the Question Forums category; Hello, First off, I want to apoligize for adding the same topic to an already popular subject. I have been ...

1. ## Combine & sum rows of data

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.

 Part No Description Qty Cost 1018058 Part 1 202 37.976 1018058 Part 1 192 36.096 1018058 Part 1 192 36.096 1018058 Part 1 204 38.352 1007346 Part 3 48 0 2018916 Part 4 20 0 2018916 Part 4 116 0 2018916 Part 4 128 0 2100497 Part 5 2 46.28 2100497 Part 5 9 208.26 2100497 Part 5 16 370.24 2087217 Part 6 11 1485 2087217 Part 6 11 1485 2087217 Part 6 12 1620 2087217 Part 6 10 1350 2026268 Part 7 4 2.0828 M010449 Part 8 10 101.459 M010449 Part 8 16 162.3344 2094686 Part 9 231 303.7419

2. ## Re: Combine & sum rows of data

[Cerberus],

Welcome to the MrExcel board.

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

Sheet1

 A B C D 1 Part No Description Qty Cost 2 1018058 Part 1 202 37.976 3 1018058 Part 1 192 36.096 4 1018058 Part 1 192 36.096 5 1018058 Part 1 204 38.352 6 1007346 Part 3 48 0 7 2018916 Part 4 20 0 8 2018916 Part 4 116 0 9 2018916 Part 4 128 0 10 2100497 Part 5 2 46.28 11 2100497 Part 5 9 208.26 12 2100497 Part 5 16 370.24 13 2087217 Part 6 11 1485 14 2087217 Part 6 11 1485 15 2087217 Part 6 12 1620 16 2087217 Part 6 10 1350 17 2026268 Part 7 4 2.0828 18 M010449 Part 8 10 101.459 19 M010449 Part 8 16 162.3344 20 2094686 Part 9 231 303.7419 21

Excel tables to the web >> Excel Jeanie HTML 4

The macro will create worksheet "Summary":

Summary

 A B C D 1 Part No Description Qty Cost 2 1018058 Part 1 790 148.52 3 1007346 Part 3 48 0 4 2018916 Part 4 264 0 5 2100497 Part 5 27 624.78 6 2087217 Part 6 44 5940 7 2026268 Part 7 4 2.0828 8 M010449 Part 8 26 263.7934 9 2094686 Part 9 231 303.7419 10

Excel tables to the web >> Excel Jeanie HTML 4

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).

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.

3. ## Re: Combine & sum rows of data

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.

4. ## Re: Combine & sum rows of data

[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

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

(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

5. ## Re: Combine & sum rows of data

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.

6. ## Re: Combine & sum rows of data

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:

Sheet1

 A B C D 1 Part No Description Qty Cost 2 1018058 Part 1 202 37.976 3 1018058 Part 1 192 36.096 4 1018058 Part 1 192 36.096 5 1018058 Part 1 204 38.352 6 1007346 Part 3 48 0 7 2018916 Part 4 20 0 8 2018916 Part 4 116 0 9 2018916 Part 4 128 0 10 2100497 Part 5 2 46.28 11 2100497 Part 5 9 208.26 12 2100497 Part 5 16 370.24 13 2087217 Part 6 11 1485 14 2087217 Part 6 11 1485 15 2087217 Part 6 12 1620 16 2087217 Part 6 10 1350 17 2026268 Part 7 4 2.0828 18 M010449 Part 8 10 101.459 19 M010449 Part 8 16 162.3344 20 2094686 Part 9 231 303.7419 21

Excel tables to the web >> Excel Jeanie HTML 4

After the macro in a new worksheet Summary:

Summary

 A B C D 1 Part No Description Qty Cost 2 1018058 Part 1 790 148.52 3 1007346 Part 3 48 0 4 2018916 Part 4 264 0 5 2100497 Part 5 27 624.78 6 2087217 Part 6 44 5940 7 2026268 Part 7 4 2.0828 8 M010449 Part 8 26 263.7934 9 2094686 Part 9 231 303.7419 10

 Cell Formula C2 =SUMPRODUCT(--(Sheet1!\$A\$2:\$A\$20=\$A2),--(Sheet1!\$B\$2:\$B\$20=\$B2),Sheet1!\$C\$2:\$C\$20) D2 =SUMPRODUCT(--(Sheet1!\$A\$2:\$A\$20=\$A2),--(Sheet1!\$B\$2:\$B\$20=\$B2),Sheet1!\$D\$2:\$D\$20) C3 =SUMPRODUCT(--(Sheet1!\$A\$2:\$A\$20=\$A3),--(Sheet1!\$B\$2:\$B\$20=\$B3),Sheet1!\$C\$2:\$C\$20) D3 =SUMPRODUCT(--(Sheet1!\$A\$2:\$A\$20=\$A3),--(Sheet1!\$B\$2:\$B\$20=\$B3),Sheet1!\$D\$2:\$D\$20) C4 =SUMPRODUCT(--(Sheet1!\$A\$2:\$A\$20=\$A4),--(Sheet1!\$B\$2:\$B\$20=\$B4),Sheet1!\$C\$2:\$C\$20) D4 =SUMPRODUCT(--(Sheet1!\$A\$2:\$A\$20=\$A4),--(Sheet1!\$B\$2:\$B\$20=\$B4),Sheet1!\$D\$2:\$D\$20) C5 =SUMPRODUCT(--(Sheet1!\$A\$2:\$A\$20=\$A5),--(Sheet1!\$B\$2:\$B\$20=\$B5),Sheet1!\$C\$2:\$C\$20) D5 =SUMPRODUCT(--(Sheet1!\$A\$2:\$A\$20=\$A5),--(Sheet1!\$B\$2:\$B\$20=\$B5),Sheet1!\$D\$2:\$D\$20) C6 =SUMPRODUCT(--(Sheet1!\$A\$2:\$A\$20=\$A6),--(Sheet1!\$B\$2:\$B\$20=\$B6),Sheet1!\$C\$2:\$C\$20) D6 =SUMPRODUCT(--(Sheet1!\$A\$2:\$A\$20=\$A6),--(Sheet1!\$B\$2:\$B\$20=\$B6),Sheet1!\$D\$2:\$D\$20) C7 =SUMPRODUCT(--(Sheet1!\$A\$2:\$A\$20=\$A7),--(Sheet1!\$B\$2:\$B\$20=\$B7),Sheet1!\$C\$2:\$C\$20) D7 =SUMPRODUCT(--(Sheet1!\$A\$2:\$A\$20=\$A7),--(Sheet1!\$B\$2:\$B\$20=\$B7),Sheet1!\$D\$2:\$D\$20) C8 =SUMPRODUCT(--(Sheet1!\$A\$2:\$A\$20=\$A8),--(Sheet1!\$B\$2:\$B\$20=\$B8),Sheet1!\$C\$2:\$C\$20) D8 =SUMPRODUCT(--(Sheet1!\$A\$2:\$A\$20=\$A8),--(Sheet1!\$B\$2:\$B\$20=\$B8),Sheet1!\$D\$2:\$D\$20) C9 =SUMPRODUCT(--(Sheet1!\$A\$2:\$A\$20=\$A9),--(Sheet1!\$B\$2:\$B\$20=\$B9),Sheet1!\$C\$2:\$C\$20) D9 =SUMPRODUCT(--(Sheet1!\$A\$2:\$A\$20=\$A9),--(Sheet1!\$B\$2:\$B\$20=\$B9),Sheet1!\$D\$2:\$D\$20)

Excel tables to the web >> Excel Jeanie HTML 4

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).

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.

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

7. ## Re: Combine & sum rows of data

RAYLWARD102,

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

8. ## Re: Combine & sum rows of data

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```

9. ## Re: Combine & sum rows of data

RAYLWARD102,

What version of Excel are you using?

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.

10. ## Re: Combine & sum rows of data

2003 right now...sometimes I use 2007

Page 1 of 4 123 ... Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•