How to remove duplicate rows based on two columns

Answer

To remove duplicate rows based on two columns in Excel, you can use five methods: the Remove Duplicates command, a helper column with concatenation, Advanced Filter, the UNIQUE function, or Power Query. Each method serves different needs depending on whether you want to permanently delete duplicates or create a separate unique list.

Remove Duplicates Command

The Remove Duplicates command is Excel's built-in feature for deleting duplicate rows directly from your dataset.

1. Select your data range including headers.
2. Go to the Data tab on the ribbon.
3. Click Remove Duplicates in the Data Tools group.
4. Uncheck Select All in the dialog box.
5. Check only the two columns you want to base duplicate detection on.
6. Click OK.

Excel deletes rows where values match in both selected columns and keeps the first occurrence of each unique combination. A dialog box displays how many duplicate values were removed and how many unique values remain.

Helper Column with Concatenation

A helper column combines two column values into one cell, allowing duplicate detection on the combined result.

1. Insert a new column next to your data.
2. Enter the formula =A2&"|"&B2 in the first cell of the helper column (adjust A2 and B2 to match your two target columns).
3. Copy the formula down to all rows in your dataset.
4. Select the helper column, copy it, and paste as values (right-click > Paste Special > Values).
5. Select your entire data range including the helper column.
6. Go to Data > Remove Duplicates.
7. Check only the helper column and click OK.
8. Delete the helper column after removing duplicates.

The delimiter "|" prevents false matches. Using =A2&B2 without a delimiter causes entries like "AB" + "C" and "A" + "BC" to appear identical since both produce "ABC". The pipe character creates distinct combinations: "AB|C" versus "A|BC".

This method is preferable when you need to verify duplicate combinations before deletion or when working with Excel versions that lack advanced functions.

Advanced Filter

Advanced Filter extracts unique rows to a new location without altering the original data.

1. Select only the two columns you want to base uniqueness on (including headers).
2. Go to Data > Sort & Filter > Advanced.
3. Select Copy to another location in the dialog box.
4. Verify the List range shows your two-column selection.
5. Enter a Copy to location (an empty cell where results will appear).
6. Check Unique records only.
7. Click OK.

Advanced Filter always evaluates all columns in the selected range when determining uniqueness. Hiding columns does not exclude them from the uniqueness check. To filter by only two columns, you must select only those two columns before applying the filter rather than selecting your entire dataset and hiding unwanted columns.

Excel copies unique rows to the specified location, and the original data remains unchanged.

UNIQUE Function (Excel 365/2021)

The UNIQUE function returns a dynamic array of unique values from a specified range without modifying the source data.

1. Click on an empty cell where you want the unique list to appear.
2. Enter the formula =UNIQUE(A2:B100) replacing A2:B100 with your two-column range.
3. Press Enter.

The function returns a dynamic array containing only unique row combinations from the specified columns. The results update automatically when source data changes. This function requires Microsoft 365 or Excel 2021.

The UNIQUE function does not modify original data. It creates a separate output that spills into adjacent cells.

Power Query

Power Query provides a non-destructive, refreshable method for removing duplicates from two columns.

1. Select your data range.
2. Go to Data > Get Data > From Table/Range.
3. Click OK in the Create Table dialog (check "My table has headers" as appropriate).
4. Hold Ctrl and click to select only the two columns in the Power Query Editor.
5. Go to Home > Remove Rows > Remove Duplicates.
6. Click Close & Load to return the cleaned data to a worksheet.

Power Query preserves the original data and creates a refreshable query. Clicking Refresh in the Data tab updates results when source data changes. This method suits recurring data cleaning tasks and large datasets.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report