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”.
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.
- 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.
- 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.
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.
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.
- 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.
- 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.
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.
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 range” dialog box will appear.
- Thirdly, select Existing Worksheet.
- Then, select B12 as the Location.
- Finally, click on OK.
Then the “PivotTable Fields” dialog 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.
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.
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.
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.
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.
- 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.
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
Get FREE Advanced Excel Exercises with Solutions!
