How to combine duplicate values in excel

Answer

To combine duplicate values in Excel, there are multiple methods depending on whether you want to sum, concatenate, or remove duplicates while keeping the data combined.

Method 1: Using the Remove Duplicates Feature

To remove duplicates and keep a single instance of each value:

1. Select the range of cells containing duplicates.

2. Go to the Data tab on the ribbon.

3. Click Remove Duplicates.

4. In the pop-up, select the columns you want to check for duplicates.

5. Click OK. Excel will keep the first instance of each value and remove the rest.

Note: This method deletes duplicates instead of combining them. Use it when unique values are needed.

Method 2: Using the SUMIF Function to Combine Numeric Duplicates

To combine or sum duplicates numerically:

1. Suppose column A contains names and column B contains amounts.

2. Use the formula in column C to sum duplicates: 

   \text{=SUMIF(A:A, A2, B:B)}

3. Drag the formula down. Column C will show the total for each duplicate name.

4. Remove duplicates in column A if you want unique names with their summed values.

Method 3: Using TEXTJOIN to Combine Text Duplicates

To combine text values for duplicates into one cell:

1. Select a blank column for the combined text.

2. Use the formula (Excel 2019 and later or Office 365):  

   \text{=TEXTJOIN(", ", TRUE, IF(A:A=A2, B:B, ""))}

3. Press Ctrl + Shift + Enter if using older Excel versions (array formula).

4. This formula will concatenate all values in column B for duplicate entries in column A, separated by a comma.

Method 4: Using a Pivot Table to Combine Duplicates

Pivot Tables are powerful for summarizing duplicates:

1. Select the data range.

2. Go to the Insert tab → PivotTable.

3. Place the duplicate column in the Rows area.

4. Place the numeric or text column in the Values area.

5. For text, choose Value Field Settings → Max or use Power Query to combine text.

Pivot Tables provide totals, counts, and combinations automatically without manual formulas.

Method 5: Using Power Query to Combine Duplicates

Power Query allows an advanced combination of duplicates, including merging text and numbers:

1. Select the data → Data tab → Get & Transform → From Table/Range.

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

3. Click Group By.

4. Choose Sum, Count, or All Rows depending on your needs.

5. Click Close & Load to return the combined results to Excel.

These methods cover numeric sums, text concatenation, and complete data consolidation. Using Pivot Tables or Power Query is preferred for large datasets to avoid complex formulas.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report