Find duplicate rows based on two column values and group

Dear All,

In the attached excel... i need to find the duplicate records based on COLUMN B & COLUMN E, if there are matching records, I have to sum the value present in COLUMN C for that combination and keep only one record.

Please see the attachment for better understanding.

Expecting an early response..

Thanks.

Try

Sub test()
    Dim a, i As Long, ii As Long, txt As String
    With Sheets("data").Cells(1).CurrentRegion
        a = .Resize(, Application.Max(.Columns.Count, 9)).Value
    End With
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(a, 1)
            txt = Join(Array(a(i, 2), a(i, 5)), Chr(2))
            If Not .exists(txt) Then
                .Item(txt) = .Count + 1
                For ii = 1 To UBound(a, 2)
                    a(.Count, ii) = a(i, ii)
                Next
            Else
                a(.Item(txt), 9) = a(.Item(txt), 9) + a(i, 9)
            End If
        Next
        i = .Count
    End With
    With Sheets.Add.Cells(1).Resize(i, UBound(a, 2))
        .Value = a: .Columns.AutoFit
    End With
End Sub

First of all thanks for your quick response..

I have tried this code, but its only removing the duplicate row and giving any output.

Originally Posted by kevivu

Dear All,

In the attached excel... i need to find the duplicate records based on COLUMN B & COLUMN E, if there are matching records, I have to sum the value present in COLUMN C for that combination and keep only one record.

Missread...
Replace 9 with 3

            Else
                a(.Item(txt), 9) = a(.Item(txt), 9) + a(i, 9)
            End If

to

            Else
                a(.Item(txt), 3) = a(.Item(txt), 3) + a(i, 3)
            End If