How to identify duplicates in two columns

Answer

To identify duplicates in two columns in Excel, you can use multiple methods depending on whether you want a visual highlight, a formula-based check, or a filtered list.

Method 1: Using Conditional Formatting

Conditional Formatting highlights duplicates automatically.

1. Select the range in the first column, for example, A2:A100.

2. Go to the Home tab.

3. Click Conditional Formatting → New Rule → Use a formula to determine which cells to format.

4. Enter the formula:

   =COUNTIF(B:B, A2)>0

5. Click Format, choose a color, and press OK.

Explanation: This formula checks each value in column A against all values in column B. If a match exists, the cell is highlighted.

6. Repeat the same process for column B if you want to highlight duplicates from column A.

Method 2: Using a Formula to Flag Duplicates

You can create a helper column to indicate duplicates.

1. In cell C2 (next to the first value in column A), enter: 

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

2. Drag the formula down to apply it to all rows in column A.

Explanation: This formula returns "Duplicate" if the value in column A exists in column B, otherwise "Unique".

Method 3: Using the MATCH Function

MATCH can identify if a value exists in another column.

1. In cell C2, enter:  

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

2. Drag down for all rows in column A.

Explanation: MATCH returns a number if it finds a match. ISNUMBER converts it to TRUE/FALSE. The IF statement outputs "Duplicate" or "Unique".

Method 4: Using Excel’s Remove Duplicates Tool (for list merging)

1. Copy both columns into a single column in a new sheet.

2. Select the combined column.

3. Go to Data → Remove Duplicates.

4. Excel will remove all duplicate values, leaving unique entries.

Note: This method permanently removes duplicates, so create a backup before applying.

Windows vs Mac Differences

• Conditional Formatting and formulas are the same.

• On Mac, the Home → Conditional Formatting → New Rule path is Format → Conditional Formatting → New Rule.

Example

Column A contains: 1,2,3, 4 
Column B contains: 3,4,5,6

Using the formula 

• $=IF(COUNTIF(B:B,A2)>0,"Duplicate","Unique")$  in column C will output:

  A2=1 → Unique  

  A3=2 → Unique  

  A4=3 → Duplicate  

  A5=4 → Duplicate

This makes identifying duplicates between two columns simple and efficient.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report