3 Suitable Ways to Combine Rows in Excel with Same ID

To summarize data or to sort data of similar values into groups, we sometimes have to combine rows that have the same ID or any common parameter in Excel. In this article, we will be looking at some approaches by which we can combine such rows with similar values.

📁 Download Excel File

Download the Excel file below.

Learn to Combine Rows in Excel with Same ID with These 3 Approaches

In this article, we will be looking at 3 approaches to combining rows with similar ID in Excel. Suppose we have a dataset where we have different books, the names of the authors, the tag on books, and the quantity present in stock in a library. This dataset will now be used to illustrate the different approaches. The dataset is shown as follows:

IF Function in Excel allows us to consolidate rows which contain text values. The basic format of IF Function is as follows:

=IF(logic_test, [value_if_true], [value_if_false])

‘logic test’ (required) – Denotes the Boolean or logical expression that must be evaluated.
‘value if true’ (optional) – The value that IF function will return if the ‘logic test’ evaluates to TRUE.
‘value if false’ (optional) – The value that the IF function will return if the ‘logic test’ evaluates to FALSE.

To consolidate rows having text values, the IF Function can be used along with helper column. To do so, we are to follow the following steps:

⬇️⬇️ STEPS ⬇️⬇️

• We select the cell range B5:C10 that contains different book names based on their author.
• We click Data then select Sort from the Ribbon.

• A popup box named Sort will appear. On the Sort by column, we select “Author” and select Order as “A to Z“.

• Data of the dataset will rearrange in the ascending order from A to Z based on the Author’s name.

• After that, we include helper column called “Booklist” and paste the following formula in the formula bar by selecting cell D6 and pressing Enter.

• Cell D6 will get filled up.

• We use Fill Handle icon at the bottom right of cell D6 and drag it down.

• Helper column named booklist will be like the following picture.

• Then we add a further helper column named “Combination” write the following formula in cell E6 and press Enter.

• After that, we drag Fill Handle icon of cell E6 up to cell E10. The result will look like the following image:

• We click to show results in helper column “Booklist” copy the cell values by pressing Ctrl+C and select the cells B6:B10.

• We right-click and paste the values as shown in the image. Alternatively, we can use the shortcut Ctrl+Alt+V.

• After that, we select the contents of the helper column in the cells E6:E10 and paste them as values.

• We delete the helper column called “Booklist” and the table looks as follows:

• Now, we select all the entries and click on Data tab on the ribbon.
• Then, select Sort from the group called Sort & Filter.

• A sort box will appear. We select Sort by Combination and the order being from Z to A and press OK.

• The combined results will move on top.

• Now we can delete the unnecessary rows and the table will finally look as follows:

📕 Read More: 6 Ways to Combine Multiple Rows in One Cell in Excel

The Tool known as Consolidate collects data from different ranges and sums up the solutions in a single output. For the datasheet given, let’s say that we have a certain number of stock of books for each author. We will now use the Consolidate Tool to gather rows having the same author and add their total stock of books. The steps are as follows:

⬇️⬇️ STEPS ⬇️⬇️

• We search Toolbar and then select Data. From Data Tools section, we select Consolidate.

• A Consolidate box will appear. We keep the function as Sum and hover over the mentioned box, place our cursor and then select the Author and Stock column. After that we press Add.
• We monitor the Top boxes row and Left column and after that, press OK.

• The new table showing the stock of books by individual authors will be shown as follows:

📕 Read More: 3 Ways to Combine Duplicate Rows and Sum Values in Excel

Another method to consolidate rows with a similar ID is by applying the VBA code. In order to follow this procedure, we need to go through the following steps:

⬇️⬇️ STEPS ⬇️⬇️

• First, we select to the Developer option and select Visual Basic.

• Then we select Insert and then select Module.

• We write the given VBA code on the right box.

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

• Now we press the Run.

• A popup box will appear to specify the cell range. We select the whole range of data and select OK.

• Rows will combine based on the same author and the result will be like the image below:

📕 Read More: 3 Ways to Convert Multiple Rows to Single Column in Excel

📄 Important Notes

🖊️  We must write the formulas carefully in the process where the IF Function is used along with the helper columns. Because assigning a wrong cell in the formula will give inconsistent results.

🖊️  We should take care to save the files with VBA Codes as Excel Macro-Enabled Workbook with the extension .xlsm. Otherwise, our VBA Code can’t be saved.

📝 Takeaways from This Article

You have taken the following summed-up inputs from the article:

📌  We can combine rows by using functions to return values or by using data tools already present in Excel.

📌  Additionally, we can also use VBA codes to easily combine rows with a common parameter. This usually works well for a large volume of data.

Conclusion

We have shown 3 ways in which we can combine rows with the same ID in Excel. This article will evidently provide you with clear insights about the topic and make you familiar with 3 different approaches to do the task. For further queries, you can comment with your thoughts. Follow ExcelDen for more informative articles.

Related Articles

• 4 Quick Ways to Merge Rows with Same Value in Excel
• 3 Ways to Merge Duplicate Rows in Excel
• 6 Suitable Ways to Convert Multiple Rows to Single Row in Excel