Page 1 of 4 123 ... LastLast
Results 1 to 10 of 31

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. #1
    New Member
    Join Date
    Nov 2009
    Posts
    2

    Default 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 NoDescriptionQtyCost
    1018058Part 120237.976
    1018058Part 119236.096
    1018058Part 119236.096
    1018058Part 120438.352
    1007346Part 3480
    2018916Part 4200
    2018916Part 41160
    2018916Part 41280
    2100497Part 5246.28
    2100497Part 59208.26
    2100497Part 516370.24
    2087217Part 6111485
    2087217Part 6111485
    2087217Part 6121620
    2087217Part 6101350
    2026268Part 742.0828
    M010449Part 810101.459
    M010449Part 816162.3344
    2094686Part 9231303.7419

  2. #2
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    11,422

    Default Re: Combine & sum rows of data

    [Cerberus],

    Welcome to the MrExcel board.

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


    Sheet1

     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    


    Excel tables to the web >> Excel Jeanie HTML 4




    The macro will create worksheet "Summary":


    Summary

     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    


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

    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.
    Have a great day,
    hiker95

    Windows 8.1, Excel 2007

  3. #3
    New Member
    Join Date
    Nov 2009
    Posts
    2

    Default 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. #4
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    11,422

    Default 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
    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
    Have a great day,
    hiker95

    Windows 8.1, Excel 2007

  5. #5
    Board Regular
    Join Date
    May 2010
    Posts
    419

    Default 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. #6
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    11,422

    Default 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

     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    


    Excel tables to the web >> Excel Jeanie HTML 4




    After the macro in a new worksheet Summary:


    Summary

     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    

    Spreadsheet Formulas
    CellFormula
    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).

    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.


    [b]If you do not want to see the SUMPRODUCT formulae on worksheet Summary I can update the code
    Have a great day,
    hiker95

    Windows 8.1, Excel 2007

  7. #7
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    11,422

    Default 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.
    Have a great day,
    hiker95

    Windows 8.1, Excel 2007

  8. #8
    Board Regular
    Join Date
    May 2010
    Posts
    419

    Default 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
    Last edited by RAYLWARD102; Sep 17th, 2010 at 07:08 PM.

  9. #9
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    11,422

    Default 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.
    Have a great day,
    hiker95

    Windows 8.1, Excel 2007

  10. #10
    Board Regular
    Join Date
    May 2010
    Posts
    419

    Default Re: Combine & sum rows of data

    2003 right now...sometimes I use 2007

Page 1 of 4 123 ... LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com