2 Easy Ways to Consolidate Rows in Your Excel

When analyzing the data in Excel, you may find it contains multiple duplicate rows. In this case, perhaps you’ll want to quickly consolidate the rows. This post will offer 2 quick means to get it.

Many users frequently need to merge the duplicate rows and sum the according values in Excel. For instance, I have a range of data in an Excel worksheet which contains a plenty of duplicate entries, like the following screenshot. Hence, I wish to consolidate the duplicate rows and sum the corresponding values in another column. It will be definitely troublesome if I manually do this. Therefore, I utilize the following 2 ways to realize it.

Method 1: Use “Consolidate” Function

1. First off, click a blank cell where you want to place the merged and summed data.
2. Then, turn to “Data” tab and click on the “Consolidate” button.
3. In the popup dialog box, ensure “Sum” is selected in “Function” box.
4. Next, click the  button.
5. Later, select the range which you want to consolidate and click  button.
6. After that, click “Add” button in “Consolidate” dialog.
7. Subsequently, check the “Top row” and “Left column” option.
8. Finally, click “OK” button.
9. At once, the rows are consolidated, as shown in the following screenshot.

Method 2: Use Excel VBA Code

1. At the very beginning, select the range that you want.
2. Then, trigger VBA editor according to “How to Run VBA Code in Your Excel“.
3. Next, copy the following VBA code into a module.

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. After that, press “F5” to run this macro now.
5. When macro finishes, a new Excel workbook will show up, in which you can see the merged rows and summed data, like the image below.

Comparison

When Encountering Excel Crash

As we all know, Excel can crash from time to time. Under this circumstance, at its worst, the current Excel file may be corrupted directly. At that time, you have no choice but to attempt Excel recovery. It demands you to either ask professionals for help or make use of a specialized Excel repair tool, such as DataNumen Excel Repair.

Author Introduction:

Shirley Zhang is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including corrupt SQL Server and outlook repair software products. For more information visit www.datanumen.com