How to Combine Duplicate Rows in Excel without Losing Data (6 Methods)

Get FREE Advanced Excel Exercises with Solutions!

We’re going to show you 6 methods of how to combine duplicate rows in Excel without losing data. To demonstrate our methods, we’ve taken a dataset with 3 columns: “Name”, “Car Model”, and “Car Maker”.

combine duplicate rows in excel without losing data


1. Merging UNIQUE, IF & TEXTJOIN to Combine Duplicate Rows in Excel Without Losing Data

For the first method, we’ll use the UNIQUE, IF, and TEXTJOIN functions to combine duplicate rows without losing data.

Steps:

  • Firstly, type the following formula in cell B13.
=UNIQUE(B5:B10)

This function will extract all the unique values from that range.

  • Secondly, press ENTER.

Then, we’ll see only the unique names in our table.

combine duplicate rows in excel without losing data

  • Thirdly, type this formula into cell C13.
=TEXTJOIN(", ",TRUE,UNIQUE(IF($B$5:$B$10=$B13,$C$5:$C$10,"")))

Formula Breakdown

  • UNIQUE(IF($B$5:$B$10=$B13,$C$5:$C$10,””))
    • Output: {“BMW”;””}.
    • Firstly, we’re checking if the name from cell B13 is in the B5:B10 range.
    • Secondly, if there is a match, then the respective value from the C5:C10 range is extracted. Else, we’ll get a blank value.
    • Finally, we’ve found only “BMW” that matched our criteria.
  • Now our formula reduces to, TEXTJOIN(“, “,TRUE,{“BMW”;””})
    • Output: “BMW”.
    • The TEXTJOIN function joins a range of strings into a single string.
    • Our delimiter is a comma (“,”). Moreover, we’ve set ignore_empty as TRUE.
    • Finally, we’re joining the text “BMW” with nothing because we’re ignoring the empty value.

  • After that, press ENTER.
  • Then, use the Fill Handle to autofill the formula.

combine duplicate rows in excel without losing data

  • Then, type the formula in cell D13.
=TEXTJOIN(", ",TRUE,UNIQUE(IF($B$5:$B$10=$B13,$D$5:$D$10,"")))

This formula is similar to the last formula. Hence, Read this again {anchor}, if you didn’t understand the formula.

  • After that, press ENTER.
  • Finally, AutoFill the formula.

combine duplicate rows in excel without losing data

Thus, we’ve achieved our goal of combining duplicate rows without losing data.

Read More: How to Merge Duplicate Rows in Excel


2. Combining Duplicate Rows in Excel by Using 2 Helper Columns

In this section, we’ll use 2 helper columns, along with the CHAR & IF functions to combine duplicate rows in Excel.

combine duplicate rows in excel without losing data

Steps:

  • Firstly, insert columns in our dataset.

  • Secondly, type the following formula in cell D5.
=IF(B5=B4,C5&CHAR(10)&D4,C5)

Formula Breakdown

  • CHAR(10) returns a line break.
  • We’re checking if the value from the lower cell is the same as the upper cell.
  • When it is the same, we’re joining the values from “Car Model” with a line break.
  • Else, we’re just keeping the cell value as it is.

combine duplicate rows in excel without losing data

  • Thirdly, press ENTER.

  • Then, AutoFill the formula.
  • After that, type the following formula in cell E5.
=IF(B6<>B5,"Combined Row","")

This time, we’re checking if the value from cell B6 is not equal to the value from cell B5. If it is true, then, we’re returning “Combined Row”, else we’ll get a blank cell.

combine duplicate rows in excel without losing data

  • Then, press ENTER and AutoFill the formula.

  • After that, we’ll copy the values from the 2 helper columns and replace them using the “Paste as Values” option.

combine duplicate rows in excel without losing data

Then, we’ll remove the extra data.

  • Firstly, we’ll delete the empty rows.
  • Secondly, replace the values of column C with column D.
  • Finally, we’ll delete the columns that we created.

In conclusion, we’ve combined duplicate rows in Excel.

combine duplicate rows in excel without losing data

Read More: Merge Data from Duplicate Rows Based on Unique Column in Excel


3. Applying PivotTable to Combine Duplicate Rows without Losing Data

In this section, we’re going to use the PivotTable to combine duplicate rows in Excel.

Steps:

  • Firstly, select the cell range B4:C10.
  • Secondly, from the Insert tab >>> select PivotTable.

The “PivotTable from table or rangedialog box will appear.

  • Thirdly, select Existing Worksheet.
  • Then, select B12 as the Location.
  • Finally, click on OK.

combine duplicate rows in excel without losing data

Then the “PivotTable Fieldsdialog box will appear.

  • Then, drag the “Name” field in Rows.
  • Drag the “Sales” field in Values.

Thus, we’ve shown you the third method of combining duplicate rows without losing data.

combine duplicate rows in excel without losing data


4. Using Consolidate Feature to Combine Duplicate Rows in Excel

We’re going to use the Consolidate feature from the Data tab to combine our duplicate rows.

Steps:

  • Firstly, select cell B13. We’ll consolidate our data here.
  • Secondly, from the Data tab >>> select Consolidate.

The dialog box of Consolidate will appear.

  • Thirdly, select the cell range B5:C10 as our “Reference:”.
  • Then select “Left column” from the “Use labels in” section.
  • Finally, press OK.

combine duplicate rows in excel without losing data

In conclusion, we’ve combined duplicate rows without losing data via the Consolidate feature.


5. Combining Duplicate Rows Without Losing Data by Using Subtotal Feature

For this section, we’ll use the Subtotal feature to combine duplicate rows. Here, we need to sort our data.

combine duplicate rows in excel without losing data

Steps: 

  • Firstly, select the cell range B5:D10.
  • Secondly, from the Data tab >>> select Sort.

A dialog box of Sort will appear. Make sure “My data has headers” is selected if you select cell range B4:D10.

  • Thirdly, select “Name” in the Sort by section.
  • Finally, press OK.

Now, we will use the Subtotal feature.

  • Firstly, select the cell range B4:D10.
  • Secondly, from the Data tab >>> select Subtotal.

The Subtotal dialog box will appear.

  • Thirdly, select these-
    • At each change in:” – “Name”.
    • Add subtotal to:” – “Sales”.
    • Put a tick mark on “Summary below data”.
  • Finally, press OK.

combine duplicate rows in excel without losing data

Thus, we’ll combine duplicate rows without losing data.


6. Using Excel UNIQUE-SUMIF Formula to Combine Duplicate Rows without Losing Data

In this last method, we’re going to use the SUMIF & UNIQUE functions to combine duplicate rows.

Steps:

  • Firstly, type the following formula in cell B13.
=UNIQUE(B5:B10)

This will extract the unique values from the cell range.

combine duplicate rows in excel without losing data

  • Secondly, press ENTER. The cells will AutoFill.
  • Thirdly, select the cell range C13:C16 and type this formula.
=SUMIF($B$5:$B$10,B13,$C$5:$C$10)

We’re checking in the cell range B5:B10 if there is a match of “Anthony”. If it is true, then we’ll sum all the values. Else, the sum operation will not start.

  • Finally, press CTRL + ENTER.

Thus, we’ve combined duplicate rows in yet another method.

combine duplicate rows in excel without losing data

Read More: Combine Duplicate Rows and Sum the Values in Excel


Things to Remember

  • The UNIQUE function is only available in Microsoft 365 & Excel 2021.
  • The TEXTJOIN function is only available from Excel 2019 version.
  • Use absolute cell references where it is necessary.
  • For text values, we can use the first 2 methods. The last 4 methods are for numbers.

Conclusion

We’ve shown you 6 methods of how to combine duplicate rows in Excel without losing data. If you face any problems understanding any of the methods, feel free to comment below for assistance. Thanks for reading, keep excelling!



<< Go Back to Merge Duplicates in Excel | Duplicates in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo