compare two excel lists for duplicates

Eliminate Excel Duplicates: Compare Two Lists

By Steven Thompson
AD

A common task in Excel is comparing two lists for duplicates to identify shared items, a powerful tool for data cleaning and organization. This process can be automated to save time and reduce errors, especially for large datasets. Let's explore how to compare two Excel lists for duplicates, step by step.

Before diving into the methods, ensure both lists are structured with the same column headers and data formats. This simplifies the comparison process and yields accurate results.

Using Conditional Formatting for Visual Identification

Conditional formatting allows you to apply visual cues to cells that meet specific criteria, helping you identify duplicates quickly.

AD
Find Duplicates in Excel Instantly | Smart Formula Trick You Must Know 💥 | Excel Tips for Beginners
Find Duplicates in Excel Instantly | Smart Formula Trick You Must Know 💥 | Excel Tips for Beginners
Even Faster Way to Compare 2 Lists in Excel [Quick Tip] » Chandoo.org - Learn Excel, Power BI & Charting Online
Even Faster Way to Compare 2 Lists in Excel [Quick Tip] » Chandoo.org - Learn Excel, Power BI & Charting Online
Compare Excel Lists Tool, Find Duplicates, Missing Items, Changes (Excel, Google Sheets)
Compare Excel Lists Tool, Find Duplicates, Missing Items, Changes (Excel, Google Sheets)

1. Select the range containing the first list (e.g., A1:C10).

Applying Conditional Formatting

1. Click on the 'Home' tab, then 'Conditional Formatting', and select 'New Rule'.

Excel – Using VLOOKUP to compare two lists
Excel – Using VLOOKUP to compare two lists
Excel List Comparison Module | Find Duplicates, Differences (Digital Download)
Excel List Comparison Module | Find Duplicates, Differences (Digital Download)
two screens showing the rows in excel
two screens showing the rows in excel

2. Choose the rule type 'Use a formula to determine which cells to format'.

3. In the 'Format Cells That Contains' field, enter the formula "=COUNTIF($A$1:$A$10,$A2)>0" (assuming your first list is in range A1:C10). This formula checks if the current cell's value appears elsewhere in column A.

AD

Formatting Duplicates

1. Click the 'Format' button and choose the formatting style for duplicate cells (e.g., fill color).

a screenshot of a spreadsheet with multiple columns
a screenshot of a spreadsheet with multiple columns
Compare Two Lists in Excel
Compare Two Lists in Excel
Find Duplicates in Excel
Find Duplicates in Excel
How to compare two lists of values in Microsoft Excel? Example
How to compare two lists of values in Microsoft Excel? Example
How to Compare Two Excel Sheets for Duplicates (5 Quick Ways) - ExcelDemy
How to Compare Two Excel Sheets for Duplicates (5 Quick Ways) - ExcelDemy
Compare Two Lists in Excel
Compare Two Lists in Excel

2. Click 'OK' to apply the rule.

Using VLOOKUP Function for Detailed Matching

VLOOKUP is an Excel function that enables you to pull specific data from one table to another based on a match. It's useful when you want to compare entire rows, not just individual columns.

AD

1. Assuming you have your two lists in columns A to C (list 1) and D to F (list 2), place the following formula in cell G2: "=IF(ISNA(VLOOKUP(D2,A2:C12,3,FALSE)),"Unique","Duplicate")". This formula checks if the value in D2 appears in the first list (A2:C12) and returns "Unique" if not found or "Duplicate" if found.

Dragging the Formula

1. After entering the formula, drag the fill handle (small square at the bottom-right of the cell) to cells G3:G100 to apply the formula to all rows in the second list.

Interpreting Results

1. Cells G2:G100 will now display "Unique" or "Duplicate" based on the comparison.

AD

2. To view only duplicates, add a filter in column G (Data tab > Filter), then uncheck the "Unique" option to hide non-matching rows.

Mastering these techniques allows you to efficiently compare two Excel lists for duplicates and maintain accurate, organized data. Regularly reviewing and updating your lists ensures optimal performance and reduces the risk of errors. Happy data cleansing!

AD