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.

With Power Query:

Please Login or Register  to view this content.

vba

Please Login or Register  to view this content.

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.

Why do you want to delete the original worksheet?

Please Login or Register  to view this content.

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)

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.

You should have mentioned this in your opening post.

Originally Posted by elbarauljr

I copied the code to the Personal workbook and opened four Workbooks (including Data 3.xlsx) to try the code.

Originally Posted by elbarauljr

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.

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!