Find duplicate values in a range
To find duplicate values in a range we need to apply the Excel IF and COUNTIF functions
Example: Find duplicate values in a range
|
The formula returns TRUE if the specified value in selected range (B5:B10) is repeated. If there are no duplicates of the specified value in the selected range, the formula will return FALSE.
Note: to apply this formula against all of the values in the selected range you will need to drag (apply) the formula across all of the rows from row 5 to row 10. |
Dim ws As Worksheet
For x = 5 To 10
ws.Range("C" & x).Value = True
Else
ws.Range("C" & x).Value = False
End If
Next x
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named Analysis.
Data Range: In this example we are finding duplicate values in range ("B5:B10"). Therefore, to use this exact VBA code you will need to capture all of the data in range ("B5:B10") that you want check for duplicate values.
ADJUSTABLE PARAMETERS
Output Range: Select the output column by changing the column reference ("C" & x) in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Data Range: Select the range that you are want to check for duplicates by changing the range reference ("B5:B10") in the VBA code to any range in the worksheet that doesn't conflict with the formula. If you change the number or location of rows then you will need to change the parameters that is driving the For Loop. In this case its the values that we have nominated for x, which are from 5 to 10.
To find duplicate values in a range we need to apply the Excel IF and COUNTIF functions.
=IF(COUNTIF(range,dup_value)>1,TRUE,FALSE)
ARGUMENTS
range: Range of cells to test for duplicate values.
dup_value: The value to check for duplicates in the specified range.