Don’t forget to share this post

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.

Layer google sheets add on offer
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE

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.
How to Consolidate Rows in Excel Step by Step Click on cell
How to Consolidate Rows in Excel (Step-by-Step) - Click on cell
  1. 2. Go to Data > Consolidate.
How to Consolidate Rows in Excel Step by Step Consolidate
How to Consolidate Rows in Excel (Step-by-Step) - Consolidate
How to Consolidate Data in Excel Step by Step
How to Consolidate Data in Excel (Step-by-Step)

If you have various Excel files but would like to access the data in one centralized worksheet, this guide is for you. Here’s how to consolidate data in Excel.

READ MORE
  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.
How to Consolidate Rows in Excel Step by Step Sum from Function
How to Consolidate Rows in Excel (Step-by-Step) - Sum from Function
  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.
How to Consolidate Rows in Excel Step by Step Consolidate Reference
How to Consolidate Rows in Excel (Step-by-Step) - Consolidate - Reference
  1. 5. Click on the “Add” button to include the cell reference in the “All references” space.
How to Consolidate Rows in Excel Step by Step All references
How to Consolidate Rows in Excel (Step-by-Step) - All references
  1. 6. Select the “Top row” and “Left column” options from the “Use labels in” group. Once you finish, click “OK” to apply changes.
How to Consolidate Rows in Excel Step by Step Use labels in
How to Consolidate Rows in Excel (Step-by-Step) - Use labels in

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.
How to Consolidate Rows in Excel Step by Step Consolidated rows
How to Consolidate Rows in Excel (Step-by-Step) - Consolidated rows

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.

How To Use Macros In Excel To Automate Tasks?

Macros are a powerful tool that can be used to automate tedious tasks in Excel. Here's how to use Macros in Excel.

READ MORE
How To Use Macros In Excel To Automate Tasks

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.
How to Consolidate Rows in Excel Step by Step Go to VBA
How to Consolidate Rows in Excel (Step-by-Step) - Go to VBA
  1. 2. Select Insert > Module.
How to Consolidate Rows in Excel Step by Step Insert Module
How to Consolidate Rows in Excel (Step-by-Step) - 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
How to Consolidate Rows in Excel Step by Step Paste Code
How to Consolidate Rows in Excel (Step-by-Step) - Paste Code
  1. 4. Press “F5” to run this macro or click on the “Play” icon, as shown below.
How to Consolidate Rows in Excel Step by Step Run macro
How to Consolidate Rows in Excel (Step-by-Step) - Run macro
  1. 5. Excel creates a new workbook with your merged rows and added data, as shown below.
How to Consolidate Rows in Excel Step by Step Summed data
How to Consolidate Rows in Excel (Step-by-Step) - Summed data

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.

How to consolidate rows in Google Sheets?

Layer is an add-on that equips you with the tools to increase efficiency and data quality in your processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds. See how it works.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Install the Layer Google Sheets Add-On today and Get Free Access to all the paid features, so you can start managing, automating, and scaling your processes on top of Google Sheets!

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:

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Nov 17 2022, Updated Nov 23 2022