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

To demonstrate our methods to combine duplicate rows without losing data, we’ll use the following dataset:

combine duplicate rows in excel without losing data


Method 1 – Merging UNIQUE, IF & TEXTJOIN Functions

Steps:

  • Enter the following formula in cell B13.
=UNIQUE(B5:B10)

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

  • Press ENTER.

Only the unique names are returned.

combine duplicate rows in excel without losing data

  • Enter this formula in 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”;””}.
    • We check if the name from cell B13 is in the B5:B10 range.
    • If there is a match, then the respective value from the C5:C10 range is extracted. Else, we’ll get a blank value.
    • Only “BMW” matches our criteria.
  • 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 join the text “BMW” with nothing because we’re ignoring the empty value.

  • Press ENTER.
  • Use the Fill Handle to autofill the formula.

combine duplicate rows in excel without losing data

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

  • Press ENTER.
  • AutoFill the formula.

combine duplicate rows in excel without losing data

We’ve achieved our goal of combining duplicate rows without losing data.

Read More: How to Merge Duplicate Rows in Excel


Method 2 – Using 2 Helper Columns

Here we’ll use 2 helper columns, along with the CHAR & IF functions.

combine duplicate rows in excel without losing data

Steps:

  • Insert columns into the dataset as in the following image:

  • Enter the following formula in cell D5:
=IF(B5=B4,C5&CHAR(10)&D4,C5)

Formula Breakdown

  • CHAR(10) returns a line break.
  • We check if the value from the lower cell is the same as the upper cell.
  • When it is the same, we join the values from “Car Model” with a line break, else we keep the cell value as it is.

combine duplicate rows in excel without losing data

  • Press ENTER.

  • AutoFill the formula.
  • Enter the following formula in cell E5:
=IF(B6<>B5,"Combined Row","")

Here we check if the value from cell B6 is not equal to the value from cell B5. If true, then we return “Combined Row”, else we’ll get a blank cell.

combine duplicate rows in excel without losing data

  • Press ENTER and AutoFill the formula.

  • 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

Now we remove the extra data.

  • Delete the empty rows.
  • Replace the values of column C with column D.
  • Delete the columns that we created.

We’ve successfully combined duplicate rows without losing data.

combine duplicate rows in excel without losing data

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


Method 3 – Using a PivotTable

Steps:

  • Select the range B4:C10.
  • From the Insert tab, select PivotTable.

The “PivotTable from table or rangedialog box will appear.

  • Select Existing Worksheet.
  • Select B12 as the Location.
  • Click on OK.

combine duplicate rows in excel without losing data

The “PivotTable Fieldsdialog box will appear.

  • Drag the “Name” field into Rows.
  • Drag the “Sales” field into Values.

Duplicate rows have been combined without losing data.

combine duplicate rows in excel without losing data


Method 4 – Using Consolidate Feature

Steps:

  • Select cell B13. We’ll consolidate our data here.
  • From the Data tab, select Consolidate.

The Consolidate dialog box will appear.

  • Select the cell range B5:C10 as “Reference:”.
  • Select “Left column” from the “Use labels in” section.
  • Click OK.

combine duplicate rows in excel without losing data

We’ve successfully combined duplicate rows without losing data.


Method 5 – Using Subtotal Feature

Here, we need to sort our data before applying the formula.

combine duplicate rows in excel without losing data

Steps: 

  • Select the range B5:D10.
  • From the Data tab, select Sort.

A Sort dialog box will appear.

  • Make sure “My data has headers” is selected if you select range B4:D10.
  • Select “Name” in the Sort by section.
  • Click OK.

Now we can use the Subtotal feature.

  • Select the range B4:D10.
  • From the Data tab, select Subtotal.

The Subtotal dialog box will appear.

  • Select these:
    • At each change in:” as “Name”.
    • Add subtotal to:” as “Sales”.
    • Tick “Summary below data”.
  • Click OK.

combine duplicate rows in excel without losing data

We’ve combined duplicate rows without losing data.


Method 6 – Combining UNIQUE and SUMIF Functions

Steps:

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

  • Press ENTER. The cells will AutoFill.
  • Select the range C13:C16 and enter this formula:
=SUMIF($B$5:$B$10,B13,$C$5:$C$10)

We check in the range B5:B10 if there is a match with “Anthony”. If it is true, then we sum all the values, else the sum operation will not execute.

  • Press CTRL + ENTER.

We’ve combined duplicate rows without any data loss.

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


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

Get FREE Advanced Excel Exercises with Solutions!
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