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.
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. Open Excel and click on the cell where you want to place the combined data result.
- 2. Go to Data > Consolidate.
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
- 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.
- 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.
- 5. Click on the “Add” button to include the cell reference in the “All references” space.
- 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.
- 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.
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
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. Select the range of cells that you’d like to consolidate and go to Developer > Visual Basic for Applications.
- 2. Select Insert > Module.
- 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
- 4. Press “F5” to run this macro or click on the “Play” icon, as shown below.
- 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.
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!
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: