How to find duplicates between two columns in excel

Answer

To find duplicates between two columns in Excel, use formulas or conditional formatting to highlight or identify matching values.

Method 1: Using a Formula

1. Assume your data is in Column A and Column B.

2. In Column C, enter the following formula in C1: 

   =IF(ISNUMBER(MATCH(A1, B:B, 0)), "Duplicate", "Unique")

3. Press Enter.

4. Drag the formula down to apply it to all rows in Column A.

Explanation:

• MATCH(A1, B:B, 0) searches for the value of A1 in Column B.

• ISNUMBER() checks if MATCH found a number (position).

• IF() returns "Duplicate" if a match exists and "Unique" if it does not.

Method 2: Using Conditional Formatting

1. Select the first column (e.g., A1:A100).

2. Go to Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.

3. Enter the formula: 

   =COUNTIF(B:B, A1)>0

4. Click Format, choose a highlight color, and click OK.

5. Click OK again to apply.

Explanation:

• COUNTIF(B:B, A1) counts how many times the value in A1 appears in Column B.

• A value greater than 0 indicates a duplicate.

• Highlighting makes duplicates visually easy to spot.

Method 3: Using the VLOOKUP Function

1. In Column C, enter the formula: 

   =IFERROR(VLOOKUP(A1, B:B, 1, FALSE), "Unique")

2. Press Enter and drag down.

Explanation:

• VLOOKUP searches for A1 in Column B.

• IFERROR returns "Unique" when no match is found.

Method 4: Using Excel's Remove Duplicates Tool (For Lists)

1. Copy Column A and Column B into a single column if needed.

2. Go to Data → Remove Duplicates.

3. Select the column and click OK.

Explanation:

• This removes duplicates but is irreversible unless data is backed up.

• Best for cleaning combined lists rather than highlighting matches.

Notes

• For Mac Excel, the steps are identical, but menu names might appear slightly different (e.g., “Home → Conditional Formatting” is the same).

• These methods work for text, numbers, and dates.

• Case sensitivity: Excel formulas are not case-sensitive.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report