How to Find Duplicate Values in Different Excel Sheets Using VBA

Finding duplicate values in different Excel sheets can be a common task, especially when dealing with large datasets. This can help you clean up data and ensure that your records are unique and accurate.

If you’re comfortable with using VBA (Visual Basic for Applications), you can automate this process to save time and prevent human error. In this tutorial, we’ll walk through using VBA to find duplicate values across different sheets within an Excel workbook.

Example

Sheet1

Sheet2

Steps

To get started, we need to outline the steps required to find duplicate values using VBA:

Open the Excel workbook with the sheets you want to compare.
Define the range of cells you wish to check for duplicates.
Write the VBA function to compare values across the sheets.
Execute the VBA script to find and highlight the duplicates.

1. Writing the VBA Function

The VBA function will loop through the defined range of cells on one sheet, and for each cell, it will compare the value against all cells in a defined range on another sheet. If a duplicate is found, it could, for example, highlight both cells with a color for easy identification.

Here’s an example of what your VBA function may look like:

Sub FindDuplicatesAcrossSheets()
    Dim wsOne As Worksheet
    Dim wsTwo As Worksheet
    Dim rangeOne As Range
    Dim rangeTwo As Range
    Dim cellOne As Range
    Dim cellTwo As Range

    ' Set worksheets
    Set wsOne = ThisWorkbook.Sheets("Sheet1")
    Set wsTwo = ThisWorkbook.Sheets("Sheet2")

    ' Define ranges to search
    Set rangeOne = wsOne.Range("A1:A10")
    Set rangeTwo = wsTwo.Range("A1:A10")

    ' Loop through all cells in rangeOne
    For Each cellOne In rangeOne
        ' Loop through all cells in rangeTwo
        For Each cellTwo In rangeTwo
            ' If value in cellOne matches cellTwo, do something
            If cellOne.Value = cellTwo.Value And Not IsEmpty(cellOne.Value) Then
                ' Change background color to yellow for both cells
                cellOne.Interior.Color = vbYellow
                cellTwo.Interior.Color = vbYellow
            End If
        Next cellTwo
    Next cellOne
End Sub

Sub FindDuplicatesAcrossSheets()

Dim wsOne As Worksheet

Dim wsTwo As Worksheet

Dim rangeOne As Range

Dim rangeTwo As Range

Dim cellOne As Range

Dim cellTwo As Range

' Set worksheets

Set wsOne = ThisWorkbook.Sheets("Sheet1")

Set wsTwo = ThisWorkbook.Sheets("Sheet2")

' Define ranges to search

Set rangeOne = wsOne.Range("A1:A10")

Set rangeTwo = wsTwo.Range("A1:A10")

' Loop through all cells in rangeOne

For Each cellOne In rangeOne

' Loop through all cells in rangeTwo

For Each cellTwo In rangeTwo

' If value in cellOne matches cellTwo, do something

If cellOne.Value = cellTwo.Value And Not IsEmpty(cellOne.Value) Then

' Change background color to yellow for both cells

cellOne.Interior.Color = vbYellow

cellTwo.Interior.Color = vbYellow

End If

Next cellTwo

Next cellOne

End Sub

Remember to customize the Worksheet names (“Sheet1”, “Sheet2”), and the Define ranges (“A1:A10”) to suit your needs.

2. Executing the VBA Script

Once you’ve written your VBA script, you’ll need to run it within the Excel VBA Editor. After running the script, any duplicate values found according to your defined parameters will be highlighted in both sheets.

Ensure you save your work before running the script to prevent data loss.

3. Customizing the VBA Script

You can expand the functionality of this script depending on your needs. For example, instead of highlighting duplicates, you might want to list them on a new sheet, delete them, or perform some other action.

Result

Sheet1

Sheet2

Conclusion

In this tutorial, we’ve learned how to create a VBA script to find duplicate values across different sheets in an Excel workbook. By customizing and running such scripts, you can significantly speed up the data-cleaning process and maintain the quality of your datasets. Remember to tailor the VBA code to fit the specifics of your workbook’s structure and your objectives.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Post Views: 3