How to Consolidate Rows in Excel (Step-by-Step)

When working with Excel spreadsheets, one of the most common tasks is to merge or combine data from different worksheets. Although this may seem like a simple procedure, it’s likely that you’ll end up with duplicate entries. If your dataset is small, these duplicates might be easier to spot and quickly removed. However, this entails manual work, and it can become overwhelming if you work with high-volume data. Excel offers two different features that can help you achieve this in a quick and efficient way.

In this article, you’ll learn how to combine duplicate rows and sum the values in Excel using three methods. First, you’ll see how to combine duplicates using the consolidate function in Excel. Second, a more advanced method involves using VBA code to merge duplicates in Excel. As a third alternative option to using Excel, you’ll learn how Layer offers powerful features to track changes, review, and consolidate data.

How to combine duplicate rows and sum the values in Excel?

Excel offers many features to help you carry out complex tasks in a quick and easy way. Here, you’ll learn how to combine duplicates in Excel following two useful methods.

Combine Duplicates with the Consolidate function in Excel

The consolidate feature in Excel can be used to consolidate multiple worksheets or rows in Excel. This is how you can combine and add rows in Excel using the consolidate function.

1. 1. Open Excel and click on the cell where you want to place the combined data result.

1. 2. Go to Data > Consolidate.

1. 3. From the “Functions” drop-down list, select “Sum”. Click on the upwards arrow button to select the cell range that you wish to consolidate.

1. 4. Excel will automatically include the range as you select it in the “Consolidate - Reference” box. Once it’s entered, click on the “button” again to return to the “Consolidate” settings window.

1. 5. Click on the “Add” button to include the cell reference in the “All references” space.

1. 6. Select the “Top row” and “Left column” options from the “Use labels in” group. Once you finish, click “OK” to apply changes.

Note: If your data range doesn’t have header rows, then leave the “Top row” option cleared.

1. 7. You should now see a new list of rows containing only unique values. Any value corresponding to the same record will be summed up.

This method is a good option if you don’t mind losing the original data; however, if you’d like to keep the original values of duplicates, then make a copy of the worksheet and apply this method in the copy version.

Merge Duplicates with VBA codes

One of the main advantages of processing data in VBA is that you can record a macro of your action and then run it every time you need to perform a similar task in the future. In other words, you have the option of automating the task of combining data. This is how you can merge duplicates using VBA in Excel.

1. 1. Select the range of cells that you’d like to consolidate and go to Developer > Visual Basic for Applications.

1. 2. Select Insert > Module.

1. 3. Copy and paste the following code into the Module Window.

Sub MergeRowsSumValues()
    Dim objSelectedRange As Excel.Range
    Dim varAddressArray As Variant
    Dim nStartRow, nEndRow As Integer
    Dim strFirstColumn, strSecondColumn As String
    Dim objDictionary As Object
    Dim nRow As Integer
    Dim objNewWorkbook As Excel.Workbook
    Dim objNewWorksheet As Excel.Worksheet
    Dim varItems, varValues As Variant

    On Error GoTo ErrorHandler
    Set objSelectedRange = Excel.Application.Selection
    varAddressArray = Split(objSelectedRange.Address(, False), ":")
    nStartRow = Split(varAddressArray(0), "$")(1)
    strFirstColumn = Split(varAddressArray(0), "$")(0)
    nEndRow = Split(varAddressArray(1), "$")(1)
    strSecondColumn = Split(varAddressArray(1), "$")(0)

    Set objDictionary = CreateObject("Scripting.Dictionary")

    For nRow = nStartRow To nEndRow
        strItem = ActiveSheet.Range(strFirstColumn & nRow).Value
        strValue = ActiveSheet.Range(strSecondColumn & nRow).Value

        If objDictionary.Exists(strItem) = False Then
            objDictionary.Add strItem, strValue
        Else
            objDictionary.Item(strItem) = objDictionary.Item(strItem) + strValue
        End If
    Next

    Set objNewWorkbook = Excel.Application.Workbooks.Add
    Set objNewWorksheet = objNewWorkbook.Sheets(1)

    varItems = objDictionary.keys
    varValues = objDictionary.items

    nRow = 0
    For i = LBound(varItems) To UBound(varItems)
        nRow = nRow + 1
        With objNewWorksheet
            .Cells(nRow, 1) = varItems(i)
            .Cells(nRow, 2) = varValues(i)
        End With
    Next
    objNewWorksheet.Columns("A:B").AutoFit

ErrorHandler:
    Exit sub
End Sub

1. 4. Press “F5” to run this macro or click on the “Play” icon, as shown below.

1. 5. Excel creates a new workbook with your merged rows and added data, as shown below.

Although Excel consolidates rows in efficient ways, you still need to put in some effort on your part. As an alternative option, Layer can help you fully automate this task, so you can focus on what really matters.

Want to Boost Your Team's Productivity and Efficiency?

Transform the way your team collaborates with Confluence, a remote-friendly workspace designed to bring knowledge and collaboration together. Say goodbye to scattered information and disjointed communication, and embrace a platform that empowers your team to accomplish more, together.

Key Features and Benefits:

• Centralized Knowledge: Access your team’s collective wisdom with ease.
• Collaborative Workspace: Foster engagement with flexible project tools.
• Seamless Communication: Connect your entire organization effortlessly.
• Preserve Ideas: Capture insights without losing them in chats or notifications.
• Comprehensive Platform: Manage all content in one organized location.
• Open Teamwork: Empower employees to contribute, share, and grow.
• Superior Integrations: Sync with tools like Slack, Jira, Trello, and more.

Limited-Time Offer: Sign up for Confluence today and claim your forever-free plan, revolutionizing your team’s collaboration experience.

Conclusion

Although the first method is easier to execute, it can’t process the two columns not next to each other. VBA is more convenient in case you want to reuse the function in the future, but it requires more level in terms of coding. Another advantage of VBA is that it won’t alter the original worksheet, as the data is merged into a new file.

You should now be able to consolidate duplicates in Excel depending on whether you’d prefer to keep the original values or not. In case you want Excel to fully consolidate rows with multiple columns, the consolidate feature is a quick and easy-to-use method. If you prefer to consolidate rows in Excel based on more complex criteria and keep the original values intact, then using the VBA code to consolidate is the best option for you. Alternatively, Layer can make this process easier and faster as all the steps will be done for you.

If you want to learn more about consolidating data in Excel, check out these articles below:

• How to Aggregate Data in Excel (Multiple Ways)
• Combine Multiple CSV Files Into One Excel File (Multiple Methods)
• How to Consolidate Data in Excel (Step-by-Step)
• How to Combine Multiple Excel Columns Into One?