Excel Combine Rows with Same ID : How to Do it Right

Suppose you’ve made a list of skills certain individuals are good with. Now, after a crash course, they’re all equipped with multiple sets of skills. Now, you need to combine the rows of data under a particular ID. Can it be done?

Yes, it IS possible to combine two rows with the same ID in Excel. It might seem a bit difficult if you don’t know your way around it. However, there are more than one way to get things done. Not to worry, we’re here to explain all the methods in detail.

So, why wait? Let’s find out exactly how to combine rows with same ID in Excel.

The Most Efficient Ways to Combine Rows with Same ID in Excel

In this writeup, we’re going to discuss four distinct methods to get the task done. You’ll have to select the best method suited to your needs among them. Not all of them are super easy to pull off, but they do work nevertheless.

Using IF Function with Helper Columns

Before we get into the practical stuff, let’s talk a bit about how the IF function works. It basically checks whether a certain condition is met. And of course, it returns one value of TRUE, and another value if FALSE.

We’re gonna use helper columns here as well. It’s used to combine rows             that contain strings or text values. Enough with the explanations, let’s see how it’s done. Just follow the steps I’m about to describe below.

1. Here we have a list of student names and the games that they play. We’re about to try and combine multiple games under one student.

Using IF Function with Helper Columns 1

2. The first thing you’ll have to do is, go to the Data tab, select the entire data set and sort “A to Z” which is under the Sort and Filter group.

Using IF Function with Helper Columns 2

3. This will sort the data in an ascending order.

4. Now, you’ll want to add a helper column 1 and add this exact formula into cell C2.

5. Then you’ll have to drag the fill handle to fill up the empty cells.

Using IF Function with Helper Columns 3

6. Now create another helper column 2 and enter this formula =IF(A2<>A3,”Merged”,””)

7. Now drag it down to see the results as seen below. Select the results in helper column 1 and copy it by using Ctrl + C.

Using IF Function with Helper Columns 4

8. Select Cell B2 and press Crrl + Alt + V. It’s going the bring up the paste special dialogue box.

9. Select Values, and press Ok.

Using IF Function with Helper Columns 5

10. Then, select the dataset under column D, copy using Ctrl + C and paste as values under the same range of column D.

11. Delete Helper column 1.

Using IF Function with Helper Columns 6

12. Select the entire dataset. Go to Sort under the Sort & Filter group of the Data tab.

Using IF Function with Helper Columns 7

13. In the popup window, set the value of Sort by to 2 and set the Order to Z to A.

Using IF Function with Helper Columns 8

14. Once you’ve done this, all the combined values will come up to the top.

Using IF Function with Helper Columns 9

15. Delete column 2 and the rows that aren’t needed.

Using IF Function with Helper Columns 10

16. And there you have it. The results that you were looking for!

Using the Consolidate Tool

Before we get into the procedure, let me tell you what the consolidate tool actually is. This tool summarizes data from various ranges and consolidates the results in a particular output range.

Using the Consolidate Tool 1

We’re about to use the dataset given above to explain how the tool is going to help us combine rows with the same ID. So, let’s get started.

1. The first thing you have to do is, go to the Data tab and select the Consolidate tool under the Data Tools group.

Using the Consolidate Tool 2

2. Now, in the popup window, we have to keep the default SUM function as it is. In the reference box, we have to select the data range.

3. Once that’s done, you’ll want to click on the Add button and check the Top row and the Left column option.

4. Then, press ok.

Using the Consolidate Tool 3

5. Once that’s done, a consolidated result of the selected data will be present.

Using the Consolidate Tool 4

Using VBA Code

Combining all rows that have the same ID is made easier with the use of a VBA code. If you have working knowledge with VBA codes, then this one is just for you.

  • First, you’ll have to press Alt + F11 to bring up the Visual Basic Editor (VBE)
  • Go to insert, and click on module.
  • Enter the VBA code below, and run it.

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

  • Save the worksheet progress and press Alt + F11 to go back again to the active worksheet.
  • Now, press Alt + F8 to bring up the macro dialog box.
  • Select the CombineRows macro, and click the run button.
  • After that, input the data range in the popup window, and press ok.
  • That should give you the desired results.

Combine Rows with Same ID Using Kutools

If you have Kutools installed in excel, you can apply the Advanced Combine Rows feature to combine rows under the same ID really fast.

  • Firstly, you have to select the rows that you want to combine together.
  • Then go to Kutools > Content > Advanced Combine Rows.
  • In the Advanced Combine Rows dialogue popup, just select the ID or the name of the column you’re willing to combine by. Then click Primary Key, and then you’ll have to specify the combination rules for other columns as you require.
  • Press OK and you’re done.

Concluding Words

These four methods actually work in real life scenarios. However, which method should you choose to apply, depends on you entirely. The first method is best for datasets that involve texts involving words. The second method is best suited to combine rows with numbers.

I hope the article helped you find what you were looking for. So, that’s all foe now. Let’s wrap this up for now.

Leave a Comment