# 3 Ways to Merge Duplicate Rows in Excel

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

## Learn to Merge Duplicate Rows in Excel with 3 Approaches

While writing this article, โMerge Duplicate Rows in Excel โ, weโve considered a dataset that contains approximately 3 columns and 15 rows. In this dataset, we added Name, and Points to make you better understand. But if you want, you can change the entities and put the values of your own.

APPROACH

### 1. Using Consolidate Feature

One of the easiest ways to merge duplicate rows in Excel is using the Consolidate option. It is effortless and simple. You can do this just by following the given steps.

โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ

• Initially, select cell E6.
• Secondly, go to the Dataย tab.
• Thirdly, choose the Consolidateย option.

• Now you will get a dialogue box named Consolidate.
• Select Sum at Function.
• Assign the Referenceย as:

\$B\$6:\$C\$15

• Select Left column as Use labels in.
• Finally, click OK.

• Consequently, you will get the result.

Undoubtedly, this is a very simple technique in Excel.

๐ Read More: 3 Ways to Convert Multiple Rows to Single Column in Excel

APPROACH

### 2. Creating Pivot Table to Merge Duplicate Rows in Excel

Another simple processย is applying Excel Pivot Table. You can do it easily. You just need to maintain the following steps.

โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ

• Primarily, select cell B6.
• Secondly, click on the Insertย tab.
• Now select PivotTable and then From Table/Range.

• Now you will get a dialogue box named PivotTable from table or range.
• Thirdly, select the Table/Range and put the following value.

โApproach 2โ!\$B\$5:\$C\$15

• Select Existing Worksheet.
• Fourthly, select Location and write the given value.

โApproach 2โ!\$E\$5

• Now press OK.

• Now you will get PivotTable1.

• Now click wherever you want on the PivotTable1.

• On the right side of the worksheet, you will get PivotTable Fields.
• Fifthly, select NAMES, and Points.
• Now select Name at Rows and Sum of Points at Values.

• Eventually, you will get the required results.

Lastly, using this technique is very unique in Excel.

๐ Read More: 4 Quick Ways to Merge Rows with Same Value in Excel

APPROACH

### 3. Removing Duplicates and Using SUMIF Function

Removing duplicates and using the SUMIF function to merge rows in Excel is a very simple method. In fact, it is one of the easiest methods. You can do it by maintaining the following steps.

โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ

• Firstly, select the entire Eย column.
• Secondly, select the Data tab.
• Thirdly, click on Remove Duplicates.

• Now you will get a dialogue box named Remove Duplicates.
• Select My data has headers.
• Select Name from Columns.
• Fourthly, click OK.

• Now you will see that all the duplicate names have been removed.

• Fifthly, select cell F6.
• Now write the following formula down on that cell.

=SUMIF(B\$6:B\$15,E6,C\$6:C\$15)

• Sixthly, tap Enter.

• Consequently, you will see the desired result.

Truly, this is a very handy method in Excel. You can also get this by searching: Combine Duplicate Rows and Sum the Values in Excel.

๐ Read More: 3 Ways to Combine Duplicate Rows and Sum Values in Excel

## How to Merge Duplicate Rows in Excel Without Losing Data

If you want to merge duplicate rows in Excel without losing data then this part is for you. You can achieve this by following the given steps using the CONCATENATE function.

โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ

• Initially, select cell C17.
• Secondly, write the following formula down on the cell.

=CONCATENATE(B6,โ,โ,B7,โ,โ,B8,โ,โ,B9,โ,โ,B10,โ,โ,B11,โ,โ,B12,โ,โ,B13,โ,โ,B14,โ,โ,B15)

• Now press Enter.
• Finally, you will get the results you required.

Undoubtedly, this is a very easy method in Excel.

## ๐ Important Notes

While performing the processes mentioned above, you need to be aware of these things:

๐๏ธย  You need to be careful while using the Consolidate option.

๐๏ธย  While applying the Excel Pivot Table, you need to be conscious about selecting the cell.

๐๏ธย  You should be careful while using the Excel functions.

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

๐ย  Firstly, weโve used the Consolidate option to merge duplicate rows in Excel.

๐ย  Secondly, we adopted Excel Pivot Table.

๐ย  Thirdly, we applied the Removing Duplicates option and used the SUMIF function.

๐ย  Finally, weโve tried to show you how to merge duplicate rows in Excel without losing data.

## Conclusion

We wish that you would be able to merge duplicate rows 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 52 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