What is the excel formula to find duplicate values in two columns

Answer

To find duplicate values in two columns in Excel, you can use a combination of the IF and COUNTIF functions.

A common approach is to compare the values in Column A against Column B and mark duplicates.

Formula: 

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

Explanation:

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

2. > 0 checks whether the value exists at least once in Column B.

3. IF(..., "Duplicate", "Unique") returns "Duplicate" if the value exists in both columns, otherwise "Unique".

Steps to apply:

1. Click on the cell next to the first value in Column A (e.g., C1).

2. Enter the formula: =IF(COUNTIF(B:B, A1) > 0, "Duplicate", "Unique").

3. Press Enter.

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

Conditional Formatting Alternative:

To highlight duplicates visually without a helper column:

1. Select Column A.

2. Go to Home → Conditional Formatting → New Rule.

3. Choose Use a formula to determine which cells to format.

4. Enter the formula: 

   =COUNTIF(B:B, A1) > 0

5. Click Format, choose a fill color, and click OK.

Notes:

• Replace A1 and B:B with the specific ranges if your data is limited, e.g., A1:A100 and B1:B100.

• This method works for text, numbers, and dates.

• For Mac users, the steps for Conditional Formatting are identical.

This approach ensures that you can quickly identify duplicates across two columns without manually checking each value.

Do you want me to provide a method that highlights duplicates in both columns simultaneously?

Yes · 0 No · 0 Add Comment · 0

Add ·

Report