How to compare two lists in excel for duplicates

Answer

To compare two lists in Excel for duplicates, use the following methods. These methods work for both Windows and Mac versions, though shortcut differences may apply.

Method 1: Using Conditional Formatting

1. Select the first list of values.

2. Go to the Home tab.

3. Click Conditional Formatting → Highlight Cells Rules → Duplicate Values.

4. Choose a formatting style (e.g., light red fill with dark red text).

5. Click OK.

Condition: Only duplicates within the selected list will be highlighted.

To compare two separate lists:

1. Select the first list (e.g., $A2:A20$).

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

3. Enter the formula:

   $=COUNTIF(\$B\$2:\$B\$20,A2)>0$
   

   Replace $\$B\$2:\$B\$20$ with the range of the second list.

4. Choose a highlight format and click OK.

Result: Values in the first list that exist in the second list are highlighted.

Method 2: Using the COUNTIF Function

1. In a blank column next to the first list, enter: 

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

   Replace $\$B\$2:\$B\$20$ with the second list range.

2. Drag the formula down to check all items.

Result: Cells labeled "Duplicate" appear for matches between the two lists.

Variation: For large datasets, using COUNTIFS allows checking multiple criteria simultaneously.

Method 3: Using the MATCH Function

1. In a blank column, enter: 

   $=IF(ISNUMBER(MATCH(A2,\$B\$2:\$B\$20,0)),"Duplicate","Unique")$
   

2. Drag the formula down for the entire list.

Explanation:

• $MATCH$ searches for the value of$A2$ in the second list.

• $ISNUMBER$ returns $TRUE$ if a match exists.

• The $IF$ function converts $TRUE$ to "Duplicate" and $FALSE$ to "Unique".

Method 4: Using VLOOKUP

1. In a blank column, enter:

   $=IF(ISNA(VLOOKUP(A2,\$B\$2:\$B\$20,1,FALSE)),"Unique","Duplicate")$
   

   Drag the formula down to all rows.

Explanation:

• $VLOOKUP$ searches for a value in the second list.

• $ISNA$ checks if the value is not found.

• $IF$ outputs "Duplicate" if found or "Unique" if not.

Tips for Efficiency

1. Sort both lists before comparison for easier visual checks.

2. Use Remove Duplicates under the Data tab to clean each list before comparison.

3. For dynamic lists, consider using Excel Tables, which automatically adjust ranges in formulas like $COUNTIF$.

4. Highlighted duplicates with conditional formatting make reviewing results faster without extra columns.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report