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.
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.
- 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.
- 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.
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.
- After selecting the table, click Data > Sort.
- Sort the table by the main column.
- Now the table looks like bellow.
- After that, we need helping columns containing the formula. One formula merges the book name.
- In Cell D5 write the formula:
- 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:
- Hit Enter and drag it down, we will see the result below.
- 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.
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.
- How to Combine Rows into One Cell in Excel (4 Methods)
- How to Combine Multiple Rows into One Cell in Excel
- Combine Duplicate Rows and Sum the Values in Excel
- Excel Formula to Count Cells with Text (Download Free Workbook)
- How to Concatenate Multiple Cells in Excel
- How to Combine Multiple Cells Into One Cell Separated By Comma In Excel (Functions & VBA)