How to compare two excel sheets for duplicates

Answer

Comparing two Excel sheets for duplicates can be done using formulas, conditional formatting, or Excel’s built-in tools. Each method provides a clear way to identify whether data exists in both sheets.

Method 1: Using the VLOOKUP Formula

1. Open both Excel sheets in the same workbook.

2. Go to the sheet where you want to check for duplicates.

3. In a blank column, enter the formula:

   =IF(ISNA(VLOOKUP(A2, Sheet2!A:A, 1, FALSE)), "Unique", "Duplicate")

• A2 is the cell you are checking.

• Sheet2!A:A is the column in the second sheet to compare.

• "Duplicate" appears if the value exists in both sheets.

• "Unique" appears if it does not exist.

4. Drag the formula down the column to apply it to all rows.

Method 2: Using the COUNTIF Formula

1. Open both sheets in the same workbook.

2. In a blank column on Sheet1, type:

   =IF(COUNTIF(Sheet2!A:A, A2)>0, "Duplicate", "Unique")

• COUNTIF counts how many times the value in A2 appears in Sheet2.

• Returns "Duplicate" if count is more than 0, "Unique" otherwise.

3. Drag the formula down to check all rows.

Method 3: Using Conditional Formatting

1. Select the range of data in Sheet1 that you want to check.

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

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

4. Enter the formula:

   =COUNTIF(Sheet2!A:A, A1)>0

5. Click Format and choose a fill color to highlight duplicates.

6. Press OK to apply formatting.

7. Duplicates in Sheet1 will now be highlighted.

On Mac, conditional formatting follows the same steps, but the menu path may slightly differ: Format > Conditional Formatting > Add Rule.

Method 4: Using Excel’s “Remove Duplicates” Tool

1. Copy data from both sheets into one new sheet.

2. Select all the data range.

3. Go to Data > Remove Duplicates.

4. Check the columns you want to compare.

5. Click OK.

6. Excel will display the number of duplicates removed, helping identify repeated entries.

Method 5: Using a Third-Party Tool or Add-In

Tools like Ablebits Duplicate Remover provide advanced options to compare multiple sheets, highlight duplicates, and export results. This is useful for large datasets.

Tips for Accuracy

• Ensure data is clean: remove extra spaces using TRIM().

• Convert all text to the same case using UPPER() or LOWER() to avoid mismatches.

• For multiple columns, combine them into one column using =A2&B2&C2 before comparing.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report