How to Combine Duplicate Rows and Sum the Values in Excel VBA

When working with large amounts of data in Excel, you may come across situations where you have duplicate rows. Instead of manually going through and calculating the totals for these duplicate entries, you can use Excel VBA (Visual Basic for Applications) to automatically combine these rows and sum their values. This can be a handy tool for data cleaning and data preparation.

Example

Step 1: Begin a New Macro

Start by creating a new macro. In Excel VBA, a macro is a piece of programming code that runs as a single task. It automates repetitive tasks, and you can design it according to your requirements.

Sub CombineRows()
    Dim i As Long
    With Worksheets("Sheet1") ' Ensure to replace "Sheet1" with your actual sheet name
        For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 2 Step -1
            ' Your code here
        Next i
    End With
End Sub

Sub CombineRows()

Dim i As Long

With Worksheets("Sheet1") ' Ensure to replace "Sheet1" with your actual sheet name

For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 2 Step -1

' Your code here

Next i

End With

End Sub

Step 2: Define Variables and Set Conditions

The variables to define would generally be related to the data in the rows you want to combine. The cell reference (“A”) indicates the column from where the VBA will start searching for duplicates. Replace “A” with the column that best suits your needs.

If Cells(i, 1) = Cells(i - 1, 1) Then

End If

If Cells(i, 1) = Cells(i - 1, 1) Then

End If

Step 3: Sum the Value of the Rows and Delete the Duplicates

The final step is to sum up the values in your specified column and delete the row. The value “2” here corresponds to the column number that contains the values to be summed. Adjust it accordingly to suit your Excel sheet.

Cells(i - 1, 2) = Cells(i, 2) + Cells(i - 1, 2)
    Rows(i).Delete
    End With
End Sub

Cells(i - 1, 2) = Cells(i, 2) + Cells(i - 1, 2)

Rows(i).Delete

End With

End Sub

Here’s the Complete Code:

Sub CombineRows()
    Dim i As Long
    With Worksheets("Sheet1") ' Ensure to replace "Sheet1" with your actual sheet name
        For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
            If Cells(i, 1) = Cells(i - 1, 1) Then
                Cells(i - 1, 2) = Cells(i, 2) + Cells(i - 1, 2)
                Rows(i).Delete
            End If
        Next i
    End With
End Sub

Sub CombineRows()

Dim i As Long

With Worksheets("Sheet1") ' Ensure to replace "Sheet1" with your actual sheet name

For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1

If Cells(i, 1) = Cells(i - 1, 1) Then

Cells(i - 1, 2) = Cells(i, 2) + Cells(i - 1, 2)

Rows(i).Delete

End If

Next i

End With

End Sub

Output

Conclusion

Excel VBA is a powerful tool that can help to streamline your data analysis process. By combining duplicate rows and summing their values, you can easily clean and structure your data. This tutorial demonstrated how to create a simple macro for this task. Remember to adjust the code to fit the structure of your specific worksheet.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Post Views: 6