5 Ways to Find Duplicates in Two Excel Sheets

Using Excel Formulas

One of the most straightforward methods to identify duplicates in two Excel sheets is through the use of formulas. Here’s how you can do it:

• VLOOKUP: This function looks for a value in the leftmost column of a table and returns a value from the same row in another column. You can use it to check if an item from Sheet1 exists in Sheet2.
• INDEX and MATCH: These two functions are often used together for more complex searches. INDEX returns the value at a given position in a range, while MATCH returns the position of a specified value within a range.
• COUNTIF: This function can count how many times a specific value appears in a range. If used across two sheets, it can identify duplicates.

Steps to Use VLOOKUP

Here’s how to use VLOOKUP to find duplicates:

1. Select a cell in the worksheet where you want the result to appear.
2. Enter the formula: =VLOOKUP(A2,Sheet2!A2:A100,1,FALSE), assuming the value you want to look up is in cell A2 of Sheet1 and the range to search is from A2 to A100 in Sheet2.
3. If VLOOKUP finds a match, it returns the value; if not, it returns #N/A. You can modify this formula to return “Duplicate” or “Unique” using an IF function.

🔍 Note: The fourth argument in VLOOKUP, FALSE, ensures an exact match. Using TRUE might give you an approximate match, which is not usually what you want when looking for duplicates.

Conditional Formatting

Conditional formatting in Excel allows you to visually mark duplicates in both sheets. Here’s how:

1. Select the range of data on Sheet1.
2. Go to Home tab > Conditional Formatting > New Rule.
3. Choose “Use a formula to determine which cells to format.”
4. Enter the formula: =COUNTIF(Sheet2!A2:A100,A2)>0 assuming the range on Sheet2 is A2:A100.
5. Set a format, like a background color, for duplicates.
6. Repeat the process for Sheet2 to find duplicates from Sheet1.

🖌 Note: Conditional formatting does not change the data but highlights the duplicates visually.

Excel Power Query

Power Query is an excellent tool for data manipulation in Excel. Here’s how to use it for finding duplicates:

1. Go to Data tab > From Other Sources > Blank Query or From Table/Range.
2. In the Query Editor, select Append Queries to combine both sheets.
3. Use Group By to count occurrences, or use Merge Queries to find duplicates.
4. Filter the results to show only items with counts greater than 1.
5. Load the results back into Excel.

Using Excel VBA

If you’re comfortable with VBA, you can automate the process:

1. Open the VBA Editor with Alt + F11.
2. Insert a new module from Insert > Module.
3. Write a macro to loop through each row of both sheets and compare values. Here’s a basic example:

Sub FindDuplicates()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim cell As Range, rng As Range

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

For Each cell In ws1.Range("A2:A100")
    If Not IsError(Application.Match(cell.Value, ws2.Range("A2:A100"), 0)) Then
        cell.Interior.Color = RGB(255, 255, 0)
    End If
Next cell


End Sub

Press F5 or run the macro to highlight duplicates.

External Tools or Add-ins




For users who prefer not to deal with formulas or coding, external tools or Excel add-ins can be a quick solution:

• Duplicate Finder: Excel add-ins like Kutools offer features to find and manage duplicates easily.
• Online Services: Websites that can upload your Excel files to compare sheets, highlighting or listing duplicates for download.

📥 Note: Be cautious when using online services, as they might not offer privacy protection or might limit functionality for free users.

These methods provide diverse approaches to solving the common task of finding duplicates across two Excel sheets, from simple formula-based solutions to more complex VBA scripts or utilizing external tools. Each method has its strengths, making it suitable for different scenarios, ensuring users can choose the one that best fits their skill level and project requirements.

What if my sheets contain large datasets?


+

For large datasets, using formulas like VLOOKUP or INDEX & MATCH might slow down your Excel, so you might prefer using Power Query or VBA for better performance.

Can I find duplicates based on multiple columns?


+

Yes, you can. Use the & operator to concatenate multiple column values in formulas like COUNTIF, or use more complex criteria in Power Query or VBA.

Is it possible to find duplicates across more than two sheets?


+

Yes, this can be done using more complex Power Query operations or by adapting VBA code to loop through multiple sheets.