Excel Combine Rows with same ID (3 Simple Methods)

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:

  1. Use Excel Consolidate data tool
  2. Use the IF function with helper columns
  3. Use Excel VBA Code

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.

Leave a Comment

Your email address will not be published. Required fields are marked *