How to Compare Two Columns in Excel and Delete Duplicates

Excel offers robust tools for data management, but comparing two columns for duplicates and removing them can be tricky. This guide provides two effective methods: using formulas and leveraging a visual wizard.

Using Excel Formulas to Compare Columns and Remove Duplicates

Scenario 1: Both Columns in the Same Sheet

1. Apply the Formula: In the first empty cell (e.g., C1), enter the formula: =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate"). This formula checks if a value in column A exists in column B. Replace A1 with the first cell of your comparison column and $B$1:$B$10000 with the range of your reference column. The dollar signs ($) ensure absolute referencing when copying the formula. You can customize “Unique” and “Duplicate” labels as needed.
   

2. Copy the Formula: Drag the fill handle (the small square at the bottom right of the cell) down to apply the formula to all rows in your comparison column. Alternatively, use keyboard shortcuts: select C1, press Ctrl+C, select the desired range, and press Ctrl+V.
   

3. Filter Duplicates: Insert a header row if needed. Apply filters by going to the Data tab and clicking Filter. Click the filter dropdown in column C, select “Duplicate,” and click OK. This displays only rows with duplicate values.
   

4. Remove or Highlight Duplicates: To delete duplicate rows, select the filtered rows, right-click, and choose Delete Row. To highlight duplicates, select the filtered cells and apply formatting using the Home tab or Ctrl+1 to open the Format Cells dialog.

Scenario 2: Columns in Different Sheets

1. Modify the Formula: Adjust the formula to reference the other sheet: =IF(ISERROR(MATCH(A1,Sheet2!$A$1:$A$10000,0)),"","Duplicate"). Replace “Sheet2” with the actual sheet name and adjust the range accordingly.

2. Follow Steps 2-4 from Scenario 1.

Using a Visual Wizard (Ablebits Dedupe Tools)

1. Launch Compare Tables: Select a cell in the first column, navigate to the Ablebits Data tab (after installing the tool), and click Compare Tables.

2. Select Columns: In the wizard, confirm the first column selection and choose the second column on Step 2.
   

3. Configure Comparison: Select “Duplicate values” and choose the column pair for comparison.

4. Handle Duplicates: Choose to delete, move, copy, highlight, or select duplicates. Moving duplicates to a separate sheet is recommended for initial use to avoid accidental data loss.

5. Finalize: Click Finish to execute the chosen action.

Conclusion

Both methods effectively compare two columns in Excel and delete duplicates. Using formulas offers a built-in solution, while Ablebits Dedupe Tools provides a more streamlined and versatile approach. Choose the method that best suits your needs and technical proficiency.