Link to home
Start Free TrialLog in
Avatar of gregfthompson
gregfthompsonFlag for Australia

asked on

Excel - script to sum cells across rows with where ID codes are duplicate, and delete original duplicate rows.

The attached example file contains three identifier columns with the main identifier being a code in column C.
There are duplicate codes at random down column C - Rows 3 & 4,  114 & 115, 185 & 186, and 196 & 197, are examples shown in red.

The objective is to have the contents of the lower duplicate row added to the contents of the cell above and then the lower row to be deleted.
The columns affected are from D to KQ.

The actual files have about 60,000 rows.
Example-file-with-duplicate-ID-code.xlsx
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Please try this and see how does it respond with approx. 60000 rows of data.
The code will work on whichever sheet is currently active. Before try this duplicate your sheet and try the code on the duplicated sheet itself.
In the attached, click the button called "Delete Duplicate Rows" on Test Sheet to run the code.

Sub SumDataAndDeleteDuplicateRow()
Dim x
Dim i As Long, j As Long
Dim lr As Long

With Application
    .Calculation = xlCalculationManual
    .EnableEvents = False
    .ScreenUpdating = False
End With

lr = Cells(Rows.Count, 3).End(xlUp).Row

x = Range("A1").CurrentRegion.Value

For i = UBound(x, 1) To 3 Step -1
    If x(i, 2) = x(i - 1, 2) And x(i, 3) = x(i - 1, 3) Then
        For j = 4 To UBound(x, 2)
            x(i - 1, j) = x(i - 1, j) + x(i, j)
        Next j
        x(i, 3) = ""
    End If
Next i
ActiveSheet.UsedRange.Cells.ClearContents
Range("A1").Resize(UBound(x, 1), UBound(x, 2)).Value = x

Range("C2:C" & lr).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

With Application
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    .ScreenUpdating = True
End With

End Sub

Open in new window

Example-file-with-duplicate-ID-code.xlsm
Avatar of gregfthompson

ASKER

Thanks.

It appears to work OK when the duplicate IDs in Column C have the same suburb name in Column B. Sometimes the suburb names are different.

In your attachment, Rows 182 and 183 are left untouched after the script is run.

Can you make an adjustment to make it work when the suburb names are different?

Thanks again.
Right now I am on mobile. Will look at it once I am back.
BTW did you try it with the large data set?
If yes, how did it perform?
Thanks.
It appeared to work but there were some where it did not. That is when I noticed that the rows that did not work had different suburb names.
There are also some where there are five or six rows where the ID in Column C is duplicated.
How about using a Pivot Table with the ID code as a row value and all the other columns as data values. This will do the adding of the multiple values on a single row.
ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks.

Great job.
You're welcome!
Thanks for the feedback.