+ Reply to Thread
Results 1 to 9 of 9

How to consolidate rows with same values on columns plus sum values on other columns

  1. #1
    Registered User
    Join Date
    12-29-2016
    Location
    United State
    MS-Off Ver
    2013
    Posts
    30

    How to consolidate rows with same values on columns plus sum values on other columns

    Hi,

    I have a spreadsheet (attached sample) with some rows that have repeated values on multiple columns.
    Is there a solution that can combine rows with the same values on columns A thru F and H thru L ?
    At the same time, the combined rows should show the sum values of the removed rows in the columns M/N/O
    Finally, it would need to create a new worksheet to paste the final result and delete the original worksheet.


    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 w/ Win7
    Posts
    562

    Re: How to consolidate rows with same values on columns plus sum values on other columns

    With Power Query:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: How to consolidate rows with same values on columns plus sum values on other columns

    vba
    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    12-29-2016
    Location
    United State
    MS-Off Ver
    2013
    Posts
    30

    Re: How to consolidate rows with same values on columns plus sum values on other columns

    I think the vba code approach from jindon is more compatible with the current data modification process.
    In the original sample, I created the "Sheet2" tab by mistake, but the idea was for the code to create the new Worksheet.

    What would be the modified code to apply in the attached sample that:

    1. creates the new Worksheet and paste the results, then delete the original Worksheet.
    2. the code should apply the actions only to the already open "Data 3" Workbook, even if I have other Workbooks open.

    Thank you.
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: How to consolidate rows with same values on columns plus sum values on other columns

    Why do you want to delete the original worksheet?
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    12-29-2016
    Location
    United State
    MS-Off Ver
    2013
    Posts
    30

    Re: How to consolidate rows with same values on columns plus sum values on other columns

    I prefer to automatically delete the original "Sheet1" because all files are received and saved back to the system with one worksheet per workbook.
    Other routines are applied to multiple workbooks based on that condition.

    I copied the code to the Personal workbook and opened four Workbooks (including Data 3.xlsx) to try the code.
    When applying the code, is shows an error and the debug highlight last line showing here. Any idea what is causing the error ?


    Sub test()
    Dim a, e, i As Long, ii As Long, txt As String, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    With ThisWorkbook.Sheets("sheet1").Cells(1).CurrentRegion
    a = .Value
    For i = 2 To UBound(a, 1)

  7. #7
    Registered User
    Join Date
    12-29-2016
    Location
    United State
    MS-Off Ver
    2013
    Posts
    30

    Re: How to consolidate rows with same values on columns plus sum values on other columns

    jindon,

    Here, I modified your original code to activate a specific workbook and automatically create the 2nd worksheet to paste the combined rows result.
    In the future, I will probably need to sum values on additional columns. How this code can be modified for example, to sum amounts on additional columns 1 and 11 ?

    Sub test()
    Dim a, e, i As Long, ii As Long, txt As String, dic As Object, ws As Worksheet
    Workbooks("Data 3.xlsx").Activate
    Set ws = Sheets.Add
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    a = Sheets("sheet1").Cells(1).CurrentRegion.Value
    For i = 2 To UBound(a, 1)
    For Each e In Array(1, 2, 3, 4, 5, 6, 8, 9, 10, 11, 12)
    txt = txt & Chr(2) & a(i, e)
    Next
    If Not dic.exists(txt) Then
    dic(txt) = dic.Count + 2
    For ii = 1 To UBound(a, 2)
    a(dic.Count + 1, ii) = a(i, ii)
    Next
    Else
    For ii = 13 To UBound(a, 2)
    a(dic(txt), ii) = a(dic(txt), ii) + a(i, ii)
    Next
    End If
    txt = ""
    Next
    With Sheets("sheet2").Cells(1).Resize(dic.Count + 1, UBound(a, 2))
    .CurrentRegion.ClearContents
    .Value = a: .Columns.AutoFit: .Parent.Select
    End With
    Application.DisplayAlerts = False
    ws.Name = Sheets("sheet1").Delete
    ws.Name = "sheet2"
    Application.DisplayAlerts = True
    End Sub


    Thanks for the help.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,051

    Re: How to consolidate rows with same values on columns plus sum values on other columns

    You should have mentioned this in your opening post.
    Quote Originally Posted by elbarauljr View Post
    I copied the code to the Personal workbook and opened four Workbooks (including Data 3.xlsx) to try the code.
    Quote Originally Posted by elbarauljr View Post
    1. creates the new Worksheet and paste the results, then delete the original Worksheet.
    2. the code should apply the actions only to the already open "Data 3" Workbook, even if I have other Workbooks open.
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    12-29-2016
    Location
    United State
    MS-Off Ver
    2013
    Posts
    30

    Re: How to consolidate rows with same values on columns plus sum values on other columns

    The original code was working, but this one is amazing because it let you select additional columns in case the workbook change later.

    Thank you for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 01-06-2020, 06:30 AM
  2. [SOLVED] How to Consolidate values from columns
    By king05 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-10-2017, 03:34 AM
  3. Automate selection of value - consolidate values from 2 columns to 1
    By 1brandeja5 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-05-2017, 10:43 AM
  4. copy values from columns to rows while skipping empty columns...
    By EricCy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-10-2016, 06:20 AM
  5. Replies: 4
    Last Post: 06-28-2012, 07:08 AM
  6. Replies: 2
    Last Post: 03-15-2012, 09:12 AM
  7. Replies: 1
    Last Post: 06-23-2009, 06:19 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1