Excel VBA Combine duplicate rows and add quantities

excel sum duplicate values only once
combine corresponding rows based on duplicate values in another column
how to sum corresponding values with same name in excel
how to combine duplicate rows and sum the values in access
how to merge duplicate cells in excel vba
excel sum up rows with same name
excel collapse rows sum
consolidate excel cells

I have data that looks like this:

Col A | Col B | Col C
name 1| Item 1|   3
name 2| Item 3|   1
name 3| Item 2|   2
name 2| Item 3|   6
name 3| Item 2|   4
name 2| Item 3|   3

And I need a line of code to add the last column of quantities for duplicate rows and then delete the duplicate rows. So the above table should look like this:

Col A | Col B | Col C
name 1| Item 1|   3
name 2| Item 3|   10
name 3| Item 2|   6

I have tried multiple ways from other people's questions, but i keep getting "error: 400".

Here's two examples:

    For Each a In tm.Range("B2", Cells(Rows.Count, "B").End(xlUp))
    For r = 1 To Cells(Rows.Count, "B").End(xlUp).Row - a.Row
        If a = a.Offset(r, 0) And a.Offset(0, 1) = a.Offset(r, 1) And a.Offset(0, 2) = a.Offset(r, 2) Then
            a.Offset(0, 4) = a.Offset(0, 4) + a.Offset(r, 4)
            a.Offset(r, 0).EntireRow.Delete
            r = r - 1
        End If
    Next r
Next a


With Worksheets("Card Test") 

With .Range("b2:e2").Resize(.Cells(.Rows.Count, 1).End(xlUp).Row)
    .Copy
    With .Offset(, .Columns.Count + 1)
        .PasteSpecial xlPasteAll ' copy value and formats
        .Columns(2).Offset(1).Resize(.Rows.Count - 1, 2).FormulaR1C1 = "=SUMIF(C1,RC1,C[-" & .Columns.Count + 1 & "])"
        .Value = .Value
        .RemoveDuplicates 1, xlYes
    End With
End With

End With

Also I should mention that I have two worksheets and the button using the macro will be on a different sheet than the data. That seems to be causing issues too.

You could use a FOR loop to solve your issue:

Sub RemoveDuplicates()

Dim lastrow As Long

lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For x = lastrow To 1 Step -1
    For y = 1 To lastrow
        If Cells(x, 1).Value = Cells(y, 1).Value And Cells(x, 2).Value = Cells(y, 2).Value And x > y Then
            Cells(y, 3).Value = Cells(x, 3).Value + Cells(y, 3).Value
            Rows(x).EntireRow.Delete
            Exit For
        End If
    Next y
Next x


End Sub

Excel VBA Combine duplicate rows and add quantities, You could use a FOR loop to solve your issue: Sub RemoveDuplicates() Dim lastrow As Long lastrow = Cells(Rows.Count, "A").End(xlUp). 4# choose Sum from Function: drop-down list, select the range that you want to combine, then click Add button to add it in the All references box. Select Top row and Left column checkbox. Then click OK button. 5# you will see that all duplicate rows are combined and the corresponding values summed. Combine Duplicate Rows and Sum the Values with VBA code

Create a code module in your workbook, by default 'Module1'. Paste the following 3 items into that module, with the Enum declaration at the very top. You can change the enumerations, like NumItem = 3 would make the column in which you have the item's name "C" and NumQty automatically 4 ("D") because it follows in the next line. Right now the columns are A, B, and C.

Private Enum Num

NumName = 1                     ' Column Names
NumItem
NumQty
NumFirstRow = 2                 ' First data row

End Enum

Sub CreateMergedList()

Dim Ws As Worksheet
Dim Comp As String, Comp1 As String
Dim R As Long, Rend As Long, Rsum As Long
Dim Qty As Single

Set Ws = Worksheets("Source")
Ws.Copy Before:=Sheets(1)

With Ws
    ' There is one caption row which is excluded from sorting
    With .UsedRange
        .Sort .Columns(NumName), Key2:=.Columns(NumItem), Header:=xlYes
        Rend = .Rows.Count
    End With

    For R = NumFirstRow To Rend - 1
        If Comp = vbNullString Then Comp = CompareString(Ws, R)
        Comp1 = CompareString(Ws, R + 1)
        If StrComp(Comp, Comp1) Then
            Comp = vbNullString
            Rsum = R + 1
        Else
            If Rsum = 0 Then Rsum = NumFirstRow
            Qty = .Cells(Rsum, NumQty).Value
            .Cells(Rsum, NumQty).Value = Qty + .Cells(R + 1, NumQty).Value
            .Cells(R + 1, NumName).Value = ""
        End If
    Next R

    For R = Rend To (NumFirstRow - 1) Step -1
        If .Cells(R, NumName).Value = "" Then .Rows(R).Delete
    Next R
End With

Application.DisplayAlerts = False
Worksheets(1).Delete
Application.DisplayAlerts = True
 End Sub

Private Function CompareString(Ws As Worksheet, R As Long) As String

With Ws.Rows(R)
    CompareString = .Cells(NumName).Value & "|" & .Cells(NumItem).Value
End With
 End Function

At the top of the main procedure, change the name of the worksheet "Source" to whatever is the name of your own worksheet in which you have Names, Items and Quantities.

The code will first make a copy of the worksheet. Then it will sort it by name and item. After that it will combine the quantities and, finally, delete the superfluous rows.

At the end of the code the copy is deleted. If you want to be prompted to permit deletion, add an apostrophe at the start of the line "Application.DisplayAlerts = False" to make that command ineffective.

Call the procedure "CreateMergedList" from the Click event of whatever button you have for that purpose. Have fun!

How to combine duplicate rows and sum the values in Excel?, The following VBA code also can help you consolidate duplicate rows in the worksheet, but at the same time, the original data will be destroyed, you need to  I have a table which has duplicates of the same person and I'm wanting to merge these down into a single row whilst summing up two columns assigned to each row of data. For example: I am wanting to merge the data in columns A to D by using column A as my 'reference point' on what to match.

you could use Dictionary object

Option Explicit

Sub main()
    Dim cell As Range, dataRng As Range
    Dim key As Variant

    With Worksheets("Card Test")
        Set dataRng = .Range("A1", .Cells(.Rows.count, "A").End(xlUp))
    End With

    With CreateObject("Scripting.Dictionary")
        For Each cell In dataRng
            key = cell.Value & "|" & cell.Offset(, 1).Value
            .item(key) = .item(key) + cell.Offset(, 2).Value
        Next
        dataRng.Resize(, 3).ClearContents
        dataRng.Resize(.count) = Application.Transpose(.Keys)
        dataRng.Resize(.count).Offset(, 2) = Application.Transpose(.Items)
        dataRng.Resize(.count).TextToColumns DataType:=xlDelimited, Other:=True, OtherChar:="|"
    End With
End Sub

How to combine multiple rows into one based on duplicates in Excel?, Combine multiple duplicate rows and sum/average the values with Kutools for The following VBA code can help you combine duplicate rows based on the  Then it will sort it by name and item. After that it will combine the quantities and, finally, delete the superfluous rows. At the end of the code the copy is deleted. If you want to be prompted to permit deletion, add an apostrophe at the start of the line "Application.DisplayAlerts = False" to make that command ineffective.

Excel VBA Combine duplicate rows and add quantities, After that it will combine the quantities and, finally, delete the superfluous rows. At the end of the code the copy is deleted. If you want to be prompted to permit  Method 1: Use “Consolidate” Function. First off, click a blank cell where you want to place the merged and summed data. Then, turn to “Data” tab and click on the “Consolidate” button. In the popup dialog box, ensure “Sum” is selected in “Function” box. Next, click the button. Later, select the range

VBA to merge duplicate rows and sum values in certain column , Excel Facts. Is there a shortcut key for strikethrough? Click here to reveal answer. Place the Excel Cursor, where you want the merged Data.Then, go to Data Tab > Consolidate. Consolidate Dialog box will appear. Here enter the Table which has the duplicated values and Click add to Add those values. Then, select the row/column, whichever has duplicate values. In this its the left column.

2 Easy Ways to Consolidate Rows in Your Excel, Hence, I wish to consolidate the duplicate rows and sum the corresponding Then, trigger VBA editor according to “How to Run VBA Code in Your Excel“. Next  Re: Merge duplicate rows and sum value. On the basis that your data starts "A2", has a max of 7 columns and you want the result based on the first 4 columns, Try this:-NB:- This code will alter your data.

Comments
  • Great, this works perfectly! All I needed to do is add the cell reference to the other sheet (tm) with the data):