How To Find Cells With Duplicate Values In Microsoft Excel

Did you know you can find cells that are duplicated between two columns in Excel? Here’s how to find cells with duplicate values in Microsoft Excel…

The following code will find duplicates of the cell A1 in column B (ranged 1-250, extend the range if required) and list the contents:

=IF(ISERROR(MATCH(A1,$B$1:$B$250,0)),””,A1)

To use this, insert the code into C1 (or elsewhere) and use the + sign in the corner of cell C1 to drag and update the cell down throughout column C. This will then update the code to point to the cell on the relevant row and find duplicates of the adjacent cell in column B within column A.

Obviously you can modify the script to find duplicates in any cell ranges or Excel spreadsheet.

Alternatively you can also achive this with the following macro:

Sub Find_Duplicate_Cells()
    Dim CompareRange As Variant, x As Variant, y As Variant
    Set CompareRange = Range(“A1:A5”)
    For Each x In Selection
        For Each y In CompareRange
            ‘ Modify the below offset to compare to whatever column you need. This code just assumes you are trying to find duplicate cells in column B (offset of ‘1’ from column A)
            If x = y Then x.Offset(0, 1) = x
        Next y
    Next x
End Sub

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)

Leave a comment

Your email address will not be published. Required fields are marked *