4 Quick Ways to Merge Rows with Same Value in Excel

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.

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:

• 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

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.

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

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.

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.

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

• 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