Excel duplicate Rows

Hello,
Can you please help,
I need to run a macro to remove duplicates and sum values
One line per city per operator.
Please see attached sample.

Your help is appreciated.
thanks
sample.xlsx
W.E.BAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Here is a code without a pivot table.

Sub CombDup()
    Dim cel As Range
    Dim i As Long
    For Each cel In Range(Range("A2"), Range("A2").End(xlDown))
        If IsEmpty(cel) Or IsEmpty(cel.Offset(1)) Then Exit Sub
        For i = cel.End(xlDown).Row To cel.Offset(1).Row Step -1
            If cel.Value = Cells(i, 1) And cel.Offset(, 1).Value = Cells(i, 2) Then
                cel.Offset(, 2).Value = Cells(i, 3) + cel.Offset(, 2).Value
                Range(Cells(i, 1), Cells(i, 3)).Delete shift:=xlUp
            End If
        Next i
    Next cel
End Sub
0
 
Saqib Husain, SyedEngineerCommented:
Have you tried a pivot table?
0
 
W.E.BAuthor Commented:
Hello,
no, I didn't try,
I need a macro to add into my already running code.

thanks,
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
regmigrantCommented:
Here's the vba to insert a pivot table which is far the simplest way to achieve what you need:

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R24C3", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet1!R17C7", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion14
    Sheets("Sheet1").Select
    Cells(17, 7).Select
    With ActiveWindow
        .Width = 963.75
        .Height = 608.25
    End With
    Range("G17").Select
    With ActiveWindow
        .Width = 1242
        .Height = 608.25
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Operator")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("City")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Count"), "Sum of Count", xlSum
    With ActiveSheet.PivotTables("PivotTable1")
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With
    Range("G18").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Operator").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
End Sub

Open in new window

0
 
W.E.BAuthor Commented:
Hello,
thank you for your help,
I get error
run-time error 5
invalid procedure call or argument.

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R24C3", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet1!R17C7", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion14

thank you
0
 
regmigrantCommented:
it works for me! - here's the code in your worksheet
Copy-of-sample.xlsm
0
 
W.E.BAuthor Commented:
Thank you all for the help,
this is exactly what I needed.
thanks
0
All Courses

From novice to tech pro — start learning today.