Sometimes you may need to summarise your dataset by combining the rows with the same ID.
In this tutorial, we will look at the following three methods you can use to combine the rows with the same ID:
How to use the Excel Consolidate tool to combine rows in Excel
The Consolidate tool summarizes data from different ranges and consolidates the results in a single output range.
We will use the following dataset to demonstrate how this tool can be used:
The dataset shows the money paid to staff at different times. We would like to know the total amount that each staff was paid.
Step 1 – Go to Data>>Consolidate:
Step 2 – In the Consolidate dialog box accept the default SUM function, under Reference select the data range, click the Add button, check the Top row and Left column options, and then click the OK button:
A consolidated summary of the data will be generated:
See Also: Excel Sort & Ignore Blanks (3 Methods Step-By-Step)
Method 2 – Use the IF function with helper columns
The Excel IF function checks whether a condition is met and returns one value if TRUE, and another value if FALSE. In conjunction with helper columns, it can be used to combine rows containing text values.
We will use the following dataset to demonstrate how this can be achieved:
The dataset shows the names of people who chair different school boards. We would like to combine the schools whose boards are chaired by the same individual.
Step 1 – Select the dataset and go to Data>>Sort A to Z:
The data is sorted in ascending order:
Step 2 – Add Helper column 1 and enter the formula =IF(A2=A1,B1&”,”&B2,B2) in cell C2:
Step 3 – Press Enter key and drag the Fill Handle button to the rest of the empty cells in the helper Column C:
Step 4 – Add Helper Column 2 and enter the formula =IF(C2<>B2,”Combined”,””) in cell D2:
Step 5 – Press the Enter key and drag down the Fill Handle to the empty cells:
Step 6 – Select the results in helper Column 1 and press Ctrl + C to copy:
Step 7 – Select cell B2 and press Ctrl + Alt + V and select Values in the Paste Special dialog box, and then click OK:
The results will be copied as values in Column B:
Step 8 – Select range D2:D8 in the helper column 2. Press Ctrl + C to copy and press Ctrl + Alt + V to paste as values in the same range D2:D8.
Step 9 – Delete helper Column 1:
Step 10 – Select the whole dataset and go to Data>>Sort:
Step 11 – In the Sort dialog box select 2 in the Sort by list box and Z to A in the Order list box and click OK:
All the combined values will come up to the top:
Step 11 – Delete the helper Column 2 and the rows that are no longer needed:
Method 3 – Use Excel VBA Code
With a working knowledge of Excel VBA, we can use Excel VBA code to easily combine all rows that have the same ID.
Step 1 – Press Alt + F11 to open the Visual Basic Editor (VBE) window.
Step 2 – In the Project window, right-click your workbook and go to Insert>>Module.
Step 3 – In the new module enter the following VBA code:
Sub CombineRows()
Dim Rng As Range
Dim xRows As Long
Dim M As Long, N As Long, O As Long
On Error Resume Next
Set Rng = Application.InputBox(“Select Range:”, “Combine Rows In Excel”, Selection.Address, , , , , 8)
Set Rng = Range(Intersect(Rng, ActiveSheet.UsedRange).Address)
If Rng Is Nothing Then Exit Sub
xRows = Rng.Rows.Count
For M = xRows To 2 Step -1
For N = 1 To M – 1
If Rng(M, 1).Value = Rng(N, 1).Value And N <> M Then
For O = 2 To Rng.Columns.Count
If Rng(M, N).Value <> “” Then
If Rng(M, O).Value = “” Then
Rng(M, O) = Rng(M, N).Value
Else
Rng(M, O) = Rng(M, O).Value & “,” & Rng(N, O).Value
End If
End If
Next
Rng(N, 1).EntireRow.Delete
M = M – 1
N = N – 1
End If
Next
Next
ActiveSheet.UsedRange.Columns.AutoFit
End Sub
Step 4 – Save the workbook and press Alt + F11 to switch to the active sheet.
Step 5 – In the active worksheet, press Alt + F8 to open the macro dialog box, select the CombineRows macro, and click the Run button:
Step 6 – Input the data range in the popup dialog box and click OK:
The rows with the same ID will be combined:
Conclusion
In this tutorial, we have demonstrated how the Consolidate tool, IF function, and Excel VBA code can be used to combine rows that have the same ID in Excel. You can use the method that best fits your situation.