How to merge duplicate rows in excel

Answer

Merging duplicate rows in Excel involves combining rows that have the same values in one or more columns while preserving unique information in other columns. This process is useful when cleaning data, summarizing information, or preparing reports.

Method 1: Using Remove Duplicates (Simplest Approach)

To eliminate duplicate rows:

1. Select the range of data where duplicates exist.

2. Go to the Data tab on the Ribbon.

3. Click Remove Duplicates.

4. In the dialog box, check the columns you want to compare for duplicates.

5. Click OK.

Note: This method deletes the duplicate rows completely. Unique data in other columns may be lost.

Method 2: Using Consolidate to Merge Values

To combine values from duplicate rows without losing data:

1. Select an empty cell where you want the merged data to appear.

2. Go to the Data tab and click Consolidate.

3. Choose a function like Sum, Average, or Count depending on how you want to merge.

4. In Reference, select the data range.

5. Check Top row and Left column if your data has headers.

6. Click OK.

This method creates a summary table combining duplicate row values mathematically.

Method 3: Using Power Query to Merge Duplicate Rows

Power Query is ideal for complex data merging:

1. Select the data range and go to Data → Get & Transform Data → From Table/Range.

2. In the Power Query editor, select the column(s) identifying duplicates.

3. Click Group By on the Home tab.

4. In the dialog box:

   • Group By: The column(s) with duplicates.

   • Operation: Choose All Rows, Sum, Count, or Text.Combine for text columns.

5. Click OK and then Close & Load to return the merged table to Excel.

Example:
If two rows have the same "Product ID," you can merge their sales numbers using Sum and combine comments using Text.Combine.

Method 4: Using Formulas to Merge Duplicate Rows

To merge duplicates while keeping all text in one cell:

1. Use the TEXTJOIN formula with IF and FILTER.

2. Suppose A2:A10 contains duplicate IDs and B2:B10 contains values: 

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

3. Press Ctrl + Shift + Enter in older versions of Excel (array formula).

4. The result combines all values from duplicate rows into a single cell.

Explanation: TEXTJOIN merges text, IF filters rows matching the duplicate, and TRUE ignores empty cells.

Key Considerations

1. Back up your data before merging duplicates.

2. Decide whether to preserve unique information or summarize it.

3. Choose formulas for dynamic merging that update automatically when new data is added.

4. Power Query is recommended for large datasets due to speed and flexibility.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report