Remove duplicate rows with sum of a column in excel

Question

Hello All,

I have an Excel 2013 spreadsheet with multiple columns (just to fit here, I hide rest columns)

I want to remove the duplicates rows by grouping data columns (A,B,C,D,E,F) but summing the values in the G column corresponding to the repeating rows as shown in below image.

Any help is highly appreciated.

Thanks

Answer 1

Hi,

Right click on any cell within the Pivot Table and under Pivot Table Options, select Classic Pivot Table Layout under Display.

Answer 2

I've made some minor mods to the macro code I supplied earlier.

Sub mcr_Collect_Unique()

    Dim ws As Worksheet, wsu As Worksheet

    Set ws = ActiveSheet

    Set wsu = Sheets.Add(after:=Sheets(Sheets.Count))

    ws.Cells(1, 1).CurrentRegion.Copy Destination:=wsu.Cells(1, 1)

    With wsu.Cells(1, 1).CurrentRegion

        With .Cells.Resize(.Rows.Count, .Columns.Count)

            .RemoveDuplicates Columns:=Array(1, 4, 5), Header:=xlYes

            Debug.Print Application.Count(wsu.Columns(6))

            With .Cells(2, 6).Resize(Application.Count(wsu.Columns(6)), 1)

                .FormulaR1C1 = "=SUMIFS('" & ws.Name & "'!C,'" & ws.Name & _

                  "'!C[-5],RC[-5],'" & ws.Name & "'!C[-2],RC[-2],'" & ws.Name & _

                  "'!C[-1],RC[-1])"

                '.Cells = .Value

            End With

        End With

    End With

End Sub

The only issue that concerns me is the level of uniqueness to determine which rows to keep. Currently, only columns A, D & E (e.g. Array(1, 4, 5),) are considered but column J's Ref Number looks like a good candidate for addition.

Answer 3

Hi,

Create a Pivot Table.  Drag columns A:F to the Row labels and column G to the Value area section.

Hope this helps.

Answer 4

Thanks Ashish.

It helped but not completely. Using Pivot Table, I am not getting desired output. How do I get columns after G column.

There is program which generates such spreadsheet once a day. Is there a way, I can automate this using VBA script ?

Appreciate your help !

Thanks

Answer 5

A Pivot Table should solve the problem.  Upload your file to OneDrive and share the link of the file here.