Excel VBA Combine duplicate rows and add quantities
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
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 StringWith 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
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.
- Great, this works perfectly! All I needed to do is add the cell reference to the other sheet (tm) with the data):