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.


Practice Workbook

Download the following workbook and exercise.


3 Simple Methods to Combine Rows with Same ID in Excel 

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.

Combine Rows with Same ID by VBA

 

STEPS:

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

Combine Rows with Same ID by VBA

♦ 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.

Use Consolidate Tool to Merge Rows in Excel

STEPS:

  • From the Toolbar, select Data > Consolidate.

Use Consolidate Tool to Merge Rows in Excel

  • 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.

Use Consolidate Tool to Merge Rows in Excel

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

Read more: How to Merge Rows in Excel

3. Insert IF Function to Combine Rows in Excel

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

Insert IF Function to Combine Rows in Excel

STEPS:

  • After selecting the table, click Data > Sort.

Insert IF Function to Combine Rows in Excel

  • Sort the table by the main column.

Insert IF Function to Combine Rows in Excel

  • Now the table looks like bellow.

Insert IF Function to Combine Rows in Excel

  • 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","")

Insert IF Function to Combine Rows in Excel

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

Insert IF Function to Combine Rows in Excel

  • At this moment, copy the results and paste them in 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.


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.


Related Readings

Nuraida Kashmin

Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. I am a Team Leader of Excel and VBA Content Developer in ExcelDemy. Here I will also post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

2 Comments
  1. This helped me a great deal. It was very difficult to find a clean solution for this problem and I ended up using the #3. Thanks very much.

Leave a reply

ExcelDemy
Logo