How to merge duplicates in excel

Answer

How to Merge Duplicates in Excel

Merging duplicates in Excel means combining rows with the same value in a specific column while consolidating other related data. Excel does not have a direct "merge duplicates" button, but you can achieve this using functions, formulas, and tools like Remove Duplicates, PivotTables, or Power Query.

Method 1: Using Remove Duplicates (Keeping One Instance)

1. Select the range of cells or the entire table where duplicates exist.

2. Go to the Data tab.

3. Click Remove Duplicates.

4. In the pop-up, select the column(s) to check for duplicates.

5. Click OK. Excel will remove duplicate rows, keeping only the first occurrence.

Note: This method keeps the first instance and removes the rest. Other column data from duplicate rows will be lost.

Method 2: Using PivotTable to Consolidate Data

1. Select the dataset.

2. Go to the Insert tab and click PivotTable.

3. Choose where to place the PivotTable (new sheet is recommended).

4. Drag the column with duplicates into Rows.

5. Drag numeric or relevant columns into Values.

6. Change the aggregation method (Sum, Average, Count) depending on your goal.

Example:
If you have sales data with duplicate product names, a PivotTable can sum total sales per product, effectively merging duplicates.

Method 3: Using Formulas to Merge Duplicate Data

1. Assume Column A has duplicates, and Column B has values to merge.

2. Use the TEXTJOIN function to combine values: 

   =TEXTJOIN(", ", TRUE, IF(A$2:A$10=A2, B$2:B$10, ""))

3. Press Ctrl + Shift + Enter if using older Excel versions.

4. This formula combines all values in Column B corresponding to each duplicate in Column A.

Method 4: Using Power Query (Best for Large Datasets)

1. Select your table and go to Data → Get & Transform → From Table/Range.

2. In Power Query, select the column with duplicates.

3. Click Group By in the ribbon.

4. Choose Advanced and select the operation for other columns (Sum, Count, All Rows, etc.).

5. Click Close & Load to import the cleaned data back into Excel.

Power Query is highly flexible. It allows merging text, summing numbers, and keeping unique entries without writing formulas manually.

Key Points to Consider

• Merging duplicates may result in data loss if values in other columns differ. Always back up the original table.

• For numeric data, aggregation functions like SUM or AVERAGE help maintain accuracy.

• For text data, TEXTJOIN or Power Query ensures all values are retained.

• Windows and Mac Excel versions are mostly similar; only Power Query may appear under Data → Get & Transform differently on Mac.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report