# Determine if item in a list is unique or a duplicate • ### Question

• (Excel 2010 VBA question)

I need to determine if an activecell.offset(x, 0).value in a list is unique or a duplicate in its own column.  I plan on doing one thing if its unique and one if its not.  Is there an easy way to go about this?

• Edited by Monday, September 12, 2011 6:46 PM
Monday, September 12, 2011 6:45 PM

• There are various approaches depending on your objective. You could assign the column of cells values to an array, and loop through comparing your value with each element in the array, eg

```Sub test()
Dim rng As Range
Dim x As Long
Set rng = ActiveCell.CurrentRegion
col = ActiveCell.Column - rng.Column + 1
x = 1
arr = rng.Columns(2).Value
For i = 1 To UBound(arr)
If UCase(arr(i, 1)) = UCase(ActiveCell.Offset(x, 0)) Then
n = n + 1
End If
Next
n = n - 1
MsgBox ActiveCell.Offset(x, 0) & vbCr & n & " dup(s)"

End Sub```

Or maybe a worksheet formula something like this
=COUNTIF(A1:A5,A2)-1

In 2007/2010 there are built in ways of finding duplicates

Peter Thornton

Monday, September 12, 2011 7:04 PM
• (Excel 2010 VBA question)

Peter Thornton has mentioned the countif function for the worksheet but you can also use the countif function in your vba code as follows. If you know that the value will always be found at least once then the first test can be eliminated.

Sub test()
Dim x As Long
Dim temp

x = 3   'For testing purposes

temp = WorksheetFunction.CountIf(Range("A1:A30"), _
ActiveCell.Offset(x, 0).Value)
If temp = 0 Then
ElseIf temp = 1 Then
MsgBox "Not a duplicate value."
Else
MsgBox "Value " & ActiveCell.Offset(x, 0).Value _
& " found " & temp & " times."
End If

End Sub

You can use a variable with the range assigned to it in the countif as follows.

Set rng = ActiveSheet.Range("A1:A30")

temp = WorksheetFunction.CountIf(rng, _
ActiveCell.Offset(x, 0).Value)

Countif Function also works with an entire column if the data in a column is not static.

temp = WorksheetFunction.CountIf(Columns("A:A"), _
ActiveCell.Offset(x, 0).Value)

Regards, OssieMac
Tuesday, September 13, 2011 4:01 AM

### All replies

• There are various approaches depending on your objective. You could assign the column of cells values to an array, and loop through comparing your value with each element in the array, eg

```Sub test()
Dim rng As Range
Dim x As Long
Set rng = ActiveCell.CurrentRegion
col = ActiveCell.Column - rng.Column + 1
x = 1
arr = rng.Columns(2).Value
For i = 1 To UBound(arr)
If UCase(arr(i, 1)) = UCase(ActiveCell.Offset(x, 0)) Then
n = n + 1
End If
Next
n = n - 1
MsgBox ActiveCell.Offset(x, 0) & vbCr & n & " dup(s)"

End Sub```

Or maybe a worksheet formula something like this
=COUNTIF(A1:A5,A2)-1

In 2007/2010 there are built in ways of finding duplicates

Peter Thornton

Monday, September 12, 2011 7:04 PM
• (Excel 2010 VBA question)

Peter Thornton has mentioned the countif function for the worksheet but you can also use the countif function in your vba code as follows. If you know that the value will always be found at least once then the first test can be eliminated.

Sub test()
Dim x As Long
Dim temp

x = 3   'For testing purposes

temp = WorksheetFunction.CountIf(Range("A1:A30"), _
ActiveCell.Offset(x, 0).Value)
If temp = 0 Then
ElseIf temp = 1 Then
MsgBox "Not a duplicate value."
Else
MsgBox "Value " & ActiveCell.Offset(x, 0).Value _
& " found " & temp & " times."
End If

End Sub

You can use a variable with the range assigned to it in the countif as follows.

Set rng = ActiveSheet.Range("A1:A30")

temp = WorksheetFunction.CountIf(rng, _
ActiveCell.Offset(x, 0).Value)

Countif Function also works with an entire column if the data in a column is not static.

temp = WorksheetFunction.CountIf(Columns("A:A"), _
ActiveCell.Offset(x, 0).Value)

Regards, OssieMac
Tuesday, September 13, 2011 4:01 AM