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.

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

Method 3 – Using a PivotTable

Steps:

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

The “PivotTable from table or range” dialog box will appear.

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

combine duplicate rows in excel without losing data

The “PivotTable Fields” dialog 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

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 | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!