How to compare two columns and remove duplicates

Answer

To compare two columns and remove duplicates in Excel, follow these steps carefully. This process ensures data integrity while eliminating repeated entries across the columns.

Step 1: Identify Duplicates Using Conditional Formatting

1. Select the first column you want to compare.

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

3. Choose a formatting style to highlight duplicates.

4. Repeat the same for the second column.

This highlights any duplicate entries between the columns for visual inspection.

Step 2: Use the COUNTIF Formula to Compare Two Columns

A more precise method uses formulas to identify duplicates. Suppose your data is in Column A and Column B, starting from row 2.

1. In Column C, enter the formula:

   $=IF(COUNTIF(B:B,A2)>0,"Duplicate","Unique")$
   

2. Drag the formula down to check all rows in Column A.

3. "Duplicate" indicates values in Column A that exist in Column B.

4. Reverse the columns to check Column B against Column A using:

   $=IF(COUNTIF(A:A,B2)>0,"Duplicate","Unique")$
   

This method works for exact matches and can handle large datasets efficiently.

Step 3: Remove Duplicates Automatically

1. Select the entire dataset or the column where duplicates exist.

2. Go to Data > Remove Duplicates.

3. Check the column(s) to scan for duplicates.

4. Click OK. Excel will remove duplicates and show the number of removed entries.

For multi-column comparison:

1. Select all relevant columns.

2. In Remove Duplicates, ensure all columns are checked.

3. Excel will remove rows where all selected column values match exactly.

Step 4: Use a Formula to Extract Unique Values

To extract unique values into a new column, use the UNIQUE function (Excel 365 and newer):

$=UNIQUE(A2:A100)$

This formula outputs all distinct values from the selected range, excluding duplicates automatically.

Step 5: Tips for Large Datasets

1. Convert your data to an Excel Table using Ctrl + T. Tables automatically adjust formulas and ranges when new data is added.

2. Use Power Query for advanced comparison and deduplication:

   • Go to Data > Get & Transform Data > From Table/Range.

   • Use Merge Queries to compare columns and filter duplicates.

   • Load the cleaned data back to the worksheet.

Step 6: Handling Case Sensitivity

Excel formulas like COUNTIF are not case-sensitive. For case-sensitive comparison, use:

$=IF(SUMPRODUCT(--EXACT(A2,B:B))>0,"Duplicate","Unique")$

This identifies duplicates only when the text case matches exactly.

Step 7: Examples

• Column A: $Apple,Banana,Orange,Mango$

• Column B: $Banana,Grape,Apple,Kiwi$

Using the COUNTIF method, results in Column C:

Removing duplicates from Column A results in only Orange and Mango remaining.

This approach ensures duplicates are identified accurately and removed efficiently without manual inspection, even for large datasets.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report