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**”.

**Table of Contents**hide

## 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
**matc**h, 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 setas*ignore_empty***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 Rows in Excel without losing Data (5 Ways)**

## 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 “**past 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**.

**Read More:** **How to Merge Rows in Excel (4 Methods)**

**Similar Readings**

**Excel Combine Rows with Same ID (3 Quick Methods)****Convert Multiple Rows to Single Row in Excel (Easiest 5 Methods)****How to Combine Multiple Rows into One Cell in Excel****Convert Multiple Rows to A Single Column in Excel (2 ways)**

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

**Read More:** **Excel Merge Rows with Same Value (4 Ways)**

## 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!