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

	Advantages	Disadvantages
Method 1	Easy to operate	Can’t process the two columns not next to each other
Method 2	1. Convenient for reuse	1. A bit difficult to understand for VBA newbies
	2.不会打乱原来的Excel表it put the merged data in the new file	2.Can’t process the two columns not next to each other

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 attemptExcel 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, includingcorrupt SQL Server和前景修复软件产品。万博体育app官方网下载更多的正ormation visitwww.circareview.com