How to merge duplicate rows and sum

Answer

Merging duplicate rows in Excel involves combining data from rows that share the same key or identifier. This process can consolidate information efficiently without losing data.

Step 1: Identify Duplicate Rows

1. Select the range of data you want to check for duplicates.

2. Go to the Home tab.

3. Click Conditional Formatting → Highlight Cells Rules → Duplicate Values.

4. Choose a formatting style to highlight duplicates.

Duplicate rows will now be visually marked, making them easier to identify.

Step 2: Remove Exact Duplicate Rows (Optional)

To remove completely identical rows:

1. Select your data range.

2. Go to the Data tab.

3. Click Remove Duplicates.

4. In the dialog box, select columns to consider for duplicates.

5. Click OK.

Excel will keep the first occurrence and remove the rest.

Step 3: Merge Data from Duplicate Rows

Merging duplicates often requires combining data from other columns. This can be done using formulas or Power Query.

Option 1: Using Formulas

Suppose column A has duplicate IDs, and column B has values to combine. Use the TEXTJOIN formula: 

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

1. Enter this formula in a new column.

2. Press Ctrl+Shift+Enter on Windows or Command+Return on Mac if using an array formula version of Excel.

3. This will concatenate all values from column B for each duplicate in column A, separated by a comma.

Option 2: Using Power Query

Power Query provides a dynamic way to merge duplicate rows:

1. Select your data range.

2. Go to Data → Get & Transform Data → From Table/Range.

3. In Power Query Editor:

   • Select the column with duplicates.

   • Click Group By.

   • Choose All Rows or Concatenate for other columns.

4. Click Close & Load to return the merged data to Excel.

Power Query ensures changes in the source data automatically update merged results.

Step 4: Verify and Clean Up

1. Check the merged rows to ensure all relevant data is included.

2. Delete unnecessary intermediate columns if formulas were used.

3. Save the workbook to preserve changes.

Example Scenario

A sales table has duplicate Customer IDs:

Using TEXTJOIN on the Product column:

This approach keeps all product information for each customer without creating extra rows.

Merging duplicate rows reduces redundancy, improves clarity, and ensures consolidated reporting.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report