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

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

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
.IndentLevel = 0
.ShrinkToFit = False
.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.

## 📄 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.

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

(Visited 63 times, 1 visits today)

#### Jonayed

Hello! My name is Jonayed. I've completed my graduation from BUET in Naval Architecture & Marine Engineering. As Excel is very interesting to me, I like to do various types of research regarding Excel. My inquisitive nature drives me to find solutions to different types of problems with Excel.

We will be happy to hear your thoughts