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.


📂  Download Excel File

Download free workbook from here:


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.

Dataset to merge duplicate rows in Excel

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.

using consolidate option to merge duplicate rows in Excel

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

Consolidate table

  • Consequently, you will get the result.

result of using consolidate option to merge duplicate rows in Excel

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.

using pivot table to merge duplicate rows in Excel

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

pivot table

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

pivot table fields to merge duplicate rows in Excel

  • Eventually, you will get the required results.

result of using Pivot table in Excel

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 column.
  • Secondly, select the Data tab.
  • Thirdly, click on Remove Duplicates.

using remove duplicates option

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

remove duplicates table

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

usinh SUMIF function

  • Consequently, you will see the desired result.

result of using the SUMIF function

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.

using the CONCATENATE function

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.


📝 Takeaways from This Article

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 50 times, 1 visits today)
Jonayed

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

Leave a reply

ExcelDen
Logo