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:

**Approach**

**1. Applying IF Function**

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

`=IF(C6=C5,B5&","&B6,B6)`

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

`=IF(D6<>B6,"Combined","")`

- 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**

**Approach**

**2. Utilizing Consolidate Tool**

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**

**Approach**

**3. Employing VBA Code**

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**