Excel Combine Rows with Same ID (3 Quick Methods)

We can easily find the required values to combine rows with the same ID in Excel. Today we are going to learn about doing the process step by step in Excel.

Excel Combine Rows with Same ID: 3 Simple Methods

1. Combine Rows with Same ID by VBA

Let’s consider I have a worksheet containing the Salesman’s name and ID and the dates they got paid. Now I Have to merge them.

STEPS:

Go to the Sheet tab and right-click on the mouse.
Select View Code.

Microsoft Visual Basic for Application window pops up.

♦ Note: You can find this window also by pressing the Alt+F11 keys.

Now in the module window, paste the following VBA codes.

Sub Combine_Rows_with_IDs()
Dim x1 As Range
Dim x2 As Long
Dim A As Long, B As Long, C As Long
On Error Resume Next
Set x1 = Application.InputBox("Select Range:", "Combine Rows with IDs", Selection.Address, , , , , 8)
Set x1 = Range(Intersect(x1, ActiveSheet.UsedRange).Address)
If x1 Is Nothing Then Exit Sub
x2 = x1.Rows.Count
For A = x2 To 2 Step -1
For B = 1 To A - 1
If x1(A, 1).Value = x1(B, 1).Value And B <> A Then
For C = 2 To x1.Columns.Count
If x1(B, C).Value <> "" Then
If x1(A, C).Value = "" Then
x1(A, C) = x1(B, C).Value
Else
x1(A, C) = x1(A, C).Value & "," & x1(B, C).Value
End If
End If
Next
x1(B, 1).EntireRow.Delete
A = A - 1
B = B - 1
End If
Next
Next
ActiveSheet.UsedRange.Columns.AutoFit
End Sub

Then to run this VBA code Click on the Run button or press the key F5.
A dialogue box pops up and selects the range of rows we want to combine.

And finally, click OK.
And we’ll get the resultant outputs as shown below.

2. Use Consolidate Tool to Merge Rows in Excel

Consolidate tool collects data from a different location, to sum up, the values. Let’s think that we have a worksheet containing the salesman’s name and salary. We are going to use the Consolidate tool to find out the total amount of one’s salary by combining the rows.

STEPS:

From the Toolbar, select Data > Consolidate.

A dialogue box pops up.
We can choose different Functions.
Now select the data range by keeping the key column in the leftmost.
After that press Add to add the references.
Tick on the Top row & Left column and press OK.

At last, you can see the summary of the data.

3. Insert IF Function to Combine Rows in Excel

The logical function IF evaluates the given conditions and gives one value for true result and another one for false. We can cause it by combining rows containing text values. Here we have a dataset (B4:C10) that contains some books from different rows according to the writer.

STEPS:

After selecting the table, click Data > Sort.

Sort the table by the main column.

Now the table looks like below.

After that, we need helping columns containing the formula. One formula merges the book name.
In Cell D5 write the formula:

=IF(B5=B4,D4&", "&C5,C5)

Hit Enter and drag the cursor.

Here is another column we use another formula that will look for the complete book name listing.
In Cell E5, write formula:

=IF(B6<>B5,"Merged","")

Hit Enter and drag it down, we will see the result below.

At this moment, copy the results and paste them into Cell D5 as values.
Again Sort the values by the last helping column in descending order.

This way we can bring all merged values to the top.

Finally, we can delete the column that is not needed.

Practice Workbook

Download the following workbook and exercise.

Combine Rows with Same ID.xlsx

Conclusion

These are the quickest way to combine rows with the same ID in Excel. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.

Excel Combine Rows with Same ID (3 Quick Methods)

Excel Combine Rows with Same ID: 3 Simple Methods

1. Combine Rows with Same ID by VBA

2. Use Consolidate Tool to Merge Rows in Excel

3. Insert IF Function to Combine Rows in Excel

Conclusion

Related Readings

Leave a reply Cancel reply