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.
Contents
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.
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.
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.
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.
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.
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.
12. Select the entire dataset. Go to Sort under the Sort & Filter group of the Data tab.
13. In the popup window, set the value of Sort by to 2 and set the Order to Z to A.
14. Once you’ve done this, all the combined values will come up to the top.
15. Delete column 2 and the rows that aren’t needed.
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.
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.
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.
5. Once that’s done, a consolidated result of the selected data will be present.
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.
Similar Post: