Merge duplicate rows and sum one column - VBA code

Ismo

New Member
Joined
Jun 2, 2012
Messages
30
In a big data sometimes I have identical rows (maybe the 'Quantity' column has different value). I would like to merge them into one and add the quantities together. I have to use B and C (I need both) to find out if these rows are identical.

For example:
A1=date B1=111222 C1=ABCD ... and G1=quantity (1)
A2=date B2=111222 C2=ABC ... and G2=quantity (1)
A3=date B3=111222 C3=ABCD ... and G3=quantity (2)
A4=date B4=111222 C4=ABCD ... and G4=quantity (1)
A5=date B5=222111 C4=ABCD ... and G5=quantity (1)

And I would like to have my final date look like this:
A1=date B1=111222 C1=ABCD ... and G1=quantity (4)
A2=date B2=111222 C2=ABC ... and G2=quantity (1)
A3=date B5=222111 C4=ABCD ... and G5=quantity (1)

Thanks in advance!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
You should be able to do this with a sumifs formula. There are a number of ways to do this, but I'd probably copy the data onto another sheet and remove the duplicates based on column b/c. Then in column G

=sumifs(sourcedata!$G$1:$G$5,sourcedata!$B$1:$B$5,"="&B1,sourcedata!$C$1:$C$5,"="&C1)
 
Upvote 0
You should be able to do this with a sumifs formula. There are a number of ways to do this, but I'd probably copy the data onto another sheet and remove the duplicates based on column b/c. Then in column G

=sumifs(sourcedata!$G$1:$G$5,sourcedata!$B$1:$B$5,"="&B1,sourcedata!$C$1:$C$5,"="&C1)
Thanks, but I need a VBA code for that as it is a part of a data handling process.

I have a code which removes the duplicates based on B and C columns, but I don't know how to tweak it to sum up column G's.

Code:
Sub ABC()
     
    Dim LASTROW As Long
    Dim I As Long
    Dim J As Long
    Dim K As Long
    Dim MyVALUE As Variant
    Application.ScreenUpdating = False
    LASTROW = Range("A" & Rows.Count).End(xlUp).Row
    For I = 1 To LASTROW - 1
        MyVALUE = Cells(I, "B") & Cells(I, "C")
        For J = I + 1 To LASTROW
            If (MyVALUE = Cells(J, "B") & Cells(J, "C")) Then
                For K = 1 To 13
                    If (Cells(I, K) = "") Then Cells(I, K) = Cells(J, K)
                Next K
                Cells(J, "A").EntireRow.Delete
            End If
        Next J
    Next I
    Application.ScreenUpdating = True
     '
End Sub
 
Upvote 0
You could enter the formula into column H on the sheet with vba, paste as values, and then run your delete process.

You can also remove duplicates like this (Edit for ranges. Currently set to delete based on duplicate in columns 2 and 3):

Code:
    ActiveSheet.Range("$A$1:$D$9").RemoveDuplicates Columns:=Array(2, 3), Header _
        :=xlNo
 
Upvote 0

Forum statistics

Threads
1,215,615
Messages
6,125,854
Members
449,266
Latest member
davinroach

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top