none
Determine if item in a list is unique or a duplicate RRS feed

  • 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
    Monday, September 12, 2011 6:45 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)-1

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

    Peter Thornton

    Monday, September 12, 2011 7:04 PM
    Moderator
  • (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
      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 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
    Moderator
  • (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
      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 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