Answered by:
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 Myysterio Monday, September 12, 2011 6:46 PM
Answers

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)1In 2007/2010 there are built in ways of finding duplicates
Peter Thornton
 Proposed as answer by Ankur Chakravarty, Hyderabad Monday, September 19, 2011 11:11 AM
 Marked as answer by danishaniModerator Saturday, February 25, 2012 5:37 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 tempx = 3 'For testing purposes
temp = WorksheetFunction.CountIf(Range("A1:A30"), _
ActiveCell.Offset(x, 0).Value)
If temp = 0 Then
MsgBox "Value not found."
ElseIf temp = 1 Then
MsgBox "Not a duplicate value."
Else
MsgBox "Value " & ActiveCell.Offset(x, 0).Value _
& " found " & temp & " times."
End IfEnd 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 Marked as answer by danishaniModerator Saturday, February 25, 2012 5:37 PM
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)1In 2007/2010 there are built in ways of finding duplicates
Peter Thornton
 Proposed as answer by Ankur Chakravarty, Hyderabad Monday, September 19, 2011 11:11 AM
 Marked as answer by danishaniModerator Saturday, February 25, 2012 5:37 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 tempx = 3 'For testing purposes
temp = WorksheetFunction.CountIf(Range("A1:A30"), _
ActiveCell.Offset(x, 0).Value)
If temp = 0 Then
MsgBox "Value not found."
ElseIf temp = 1 Then
MsgBox "Not a duplicate value."
Else
MsgBox "Value " & ActiveCell.Offset(x, 0).Value _
& " found " & temp & " times."
End IfEnd 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 Marked as answer by danishaniModerator Saturday, February 25, 2012 5:37 PM