Sometimes we need to merge rows with the same value in** Excel**. In this article, we are going to show you how to **merge duplicate rows** in **Excel** with 4 easy approaches. We have also included some relevant concerns at the end of this article. So, let’s get started.

## 📂 Download Excel File

Download free workbook from here:

## Learn to Merge Rows with Same Value in Excel with 4 Approaches

While writing this article, “Merge Rows with the Same Value in **Excel** ”, we’ve considered a dataset that contains approximately **4** columns and **14** rows. In this dataset, we added** Names**, **Subjects**, and **Marks** to make you better understand. But if you want, you can change the entities and put the values of your own.

**APPROACH**

### 1. Applying Consolidate Command

The **Consolidate** command in **Excel** is one of the simplest ways to merge rows with same value. It is very easy and straightforward. You can accomplish this by simply following the instructions.

⬇️⬇️ **STEPS** ⬇️⬇️

- Firstly, choose cell
**F6**. - Next, navigate to the
**Data**tab. - Thirdly, select the
**Consolidate**option.

- Now you will find a dialogue box named
**Consolidate**. - Fourthly, select
**Sum**at**Function**. - Assign the
**Reference**as:

**‘Approach 1’!$B$6:$D$14**

- Again
**Add**the above reference to the**All References**. - Select the
**Left column**as**Use labels in**. - Finally, click
**OK**.

- Consequently, you will get the result.

Undoubtedly, this is a straightforward method to merge with the same value in **Excel**.

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

**APPROACH**

### 2. Merging Rows Using Formula

Another way to merge rows with the same value in **Excel** is by using a formula with the **IF **function. It is a very easy and simple technique. You can easily obtain the result just by following some simple steps.

⬇️⬇️ **STEPS** ⬇️⬇️

- Initially, select cell
**E6**. - Secondly, put the following formula on that cell.

**=IF(B6=B5,E5&”, “&C6,C6)**

- Thirdly, tap
**Enter**.

- Now drag the formula down to the entire column.
- Eventually, you will see the required result.

Truly, this procedure is very handy to merge rows with the same value in **Excel**.

**APPROACH**

### 3. Using Merge & Center Option in Top RIbbon

You can merge rows with the same value in **Excel **by using the merge **Ribbon**.

⬇️⬇️ **STEPS** ⬇️⬇️

- Initially, select cells
**B6**and**B7**. - Secondly, go to the
**Home**tab. - Select the
**Merge**ribbon and click on**Merge & Center**.

- You will find a box named
**Microsoft Excel**. - Thirdly, click on
**OK**.

- Consequently, you will get the required result.

Truly, this is a very useful method to merge rows with the same value in **Excel **as well.

**APPROACH**

### 4. Using VBA Code

Using **VBA** code is a very easy process to merge rows with same value in **Excel**. You can achieve this by doing some simple steps given below.

⬇️⬇️ **STEPS** ⬇️⬇️

- Primarily, select cells
**B6**to**B14**. - Secondly, go to the
**Data**tab. - Then click on
**Sort**.

- From the
**Sort**box, select**Name**at**Sort by**. - Then click
**OK**.

- Now you will see all the names are sorted alphabetically.

- Secondly, press
**Alt**+**F11**. - You will find a
**VBA**window. - Thirdly, click on
**Sheet5 (Approach 4)**.

- Fourthly, write the given code on that window and click
**Run**.

**Code**

```
Sub tk()
Range("B6:B8").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
End Sub
```

- Finally, you will get the desired result as well.

Undoubtedly, this is a convenient and easy technique to merge with same value in **Excel**.

## How to Merge Rows Based on Criteria in Excel

You’ve come to the right place if you’re looking for the simplest ways to merge rows in **Excel** based on criteria. In this section, I will try to demonstrate how to merge rows in **Excel** based on criteria.

⬇️⬇️ **STEPS** ⬇️⬇️

- Primarily, select cell
**F6**. Then it would help if you first used the following function.**AMPERSAND**and**COUNTIF**function were used in this case.

**=B6&” “&COUNTIF($B$6:$B6,B6)**

- Here the function implies
**COUNTIF(criteria range, criteria)**and**&**is the summation of text and space. - Secondly, the following result will show up after entering the formula and dragging it all to the
**Combined**column.

- Now, on cell
**G6**, perform the following logical function.

**=IF($B6&” “&1=F6, INDEX(C:C,MATCH($B6&” “&1,F:F,0))&” “&IFERROR(INDEX(C:C,MATCH($B6&” “&2,F:F,0)),””)&” “&IFERROR(INDEX(C:C,MATCH($B6&” “&3,F:F,0)),””)&” “&IFERROR(INDEX(C:C,MATCH($B6&” “&4,F:F,0)),””),””)**

**🔨 Formula Breakdown **

Here-

👉 **INDEX(C:C,MATCH($B6&” “&4,F:F,0)) **is the main logic here. **$B6 **is the cell reference and **& **summarizes the text and space. The **INDEX** function extracts the values of the **C** column corresponding to the **B6 **cell and returns it if the values match or not.

👉 **IFERROR(INDEX(C:C,MATCH($B6&” “&3,F:F,0)),””) **means **IFERROR **returns the formula’s result unless the formula evaluates to an error, in which case it returns a value. Here the formula will work for the **F** column.

👉 **IF($B6&” “&1=F6, INDEX(C:C,MATCH($B6&” “&1,F:F,0)) **will give us the result. If the formula matches with the corresponding cell then it will show us the result, otherwise, the cell will remain empty.

- Finally, tap
**Enter**. - This will give you the desired result.

Lastly, we must say that this technique is very simple to merge rows in **Excel **based on criteria as **IF**, **INDEX**, **IFERROR**, and **MATCH **functions have been used here.

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

## 📄 Important Notes

You should be aware of the following things while performing the processes mentioned above:

🖊️ You should be cautious when using the **Consolidate** option.

🖊️ While using the **Excel **formula, you need to be careful.

🖊️ You should apply the **VBA **code with caution.

## 📝 Takeaways from This Article

If we summarize the whole article, we have got some points.

📌 Firstly, we’ve applied the **Consolidate **command to merge rows with same value in **Excel**.

📌 Secondly, we used the **Excel **formula for merging rows.

📌 Afterward, we adopted **Excel **ribbon to merge rows with same value.

📌 Belatedly, we applied the **VBA **code.

📌 Finally, we’ve tried to show you how to merge rows in **Excel** based on criteria.

## Conclusion

We wish that you would be able to merge rows with same value in **Excel** using this article. As several methods have been shown here, you can easily follow which one is best suited for you. If there is any skepticism in your mind, feel free to ask me in the comment. In the end, to learn more about **Excel** tactics, we recommend you to visit our website **www.ExcelDen.com**.

## Related Articles

**6 Suitable Ways to Convert Multiple Rows to Single Row in Excel****3 Suitable Ways to Combine Rows in Excel with Same ID****6 Ways to Combine Multiple Rows in One Cell in Excel**