How to combine duplicate rows in excel

Answer

Combining duplicate rows in Excel is done to consolidate data, sum values, or merge text from rows that share the same key.

Step 1: Identify Duplicate Rows

1. Select the column or columns that may contain duplicates.

2. Go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values.

3. Choose a formatting style and click OK.
   This will highlight all duplicate entries for easier identification.

Step 2: Remove Exact Duplicate Rows (Optional)

1. Select the entire data range.

2. Go to Data → Remove Duplicates.

3. Choose the columns to check for duplicates.

4. Click OK.
   This removes entire duplicate rows, keeping only one instance of each.

Step 3: Combine Data for Duplicates

To combine data rather than delete duplicates, use Pivot Tables or Formulas.

Using a Pivot Table

1. Select the entire data range.

2. Go to Insert → Pivot Table → New Worksheet.

3. Drag the column with duplicates to Rows.

4. Drag numeric columns to Values and choose Sum or Average.

5. Drag text columns to Values, then change Value Field Settings → Summarize by → Max or use a formula helper column.

Using Formulas (SUMIF, TEXTJOIN)

• Summing numeric values for duplicates: 

  =SUMIF(A:A, A2, B:B)

Explanation: Sums values in column B where column A matches the value in A2.

• Combining text for duplicates (Excel 2019 and later or Excel 365): 

  =TEXTJOIN(", ", TRUE, IF(A:A=A2, C:C, ""))

Explanation: Combines text from column C where column A matches A2, separated by a comma. Use Ctrl+Shift+Enter in older versions to enter as an array formula.

Step 4: Remove Original Rows (Optional)

After combining, filter the original column and delete rows that were duplicates to avoid redundant data.

Step 5: Verify the Combined Data

Ensure all duplicate entries are properly consolidated, and sums or concatenations match expected results.

Notes

• On Mac, menus are the same, but Data → Remove Duplicates may appear under Tools → Remove Duplicates depending on the Excel version.

• TEXTJOIN is not available in versions earlier than Excel 2019.

This method ensures that duplicates are consolidated while preserving all important numeric and text data.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report