Custom Excel Function to compare the data in two ranges and count unique values RRS feed

  • Question

  • I have been trying to write a function to compare unique values, in order to see what recurrence there is in multiple data sets, and this is what I have.

    Function CountUnique(range1 As Range, range2 As Range)

    Dim TheCount As Long

    TheCount = 0

    Dim range3 As Variant, range4 As Variant
    range3 = range1.Value
    range4 = range2.Value
    For i = 1 To UBound(range3)
        For j = 1 To UBound(range4)
            If range3(i, 1) = range4(j, 1) Then
                TheCount = TheCount + 1
                End If
            Next j
        Next i

    CountUnique = TheCount

    End Function

    The code compiles fine but I am getting a #Value error back.

    Thanks in advance for any help.

    Thursday, May 23, 2013 8:15 PM

All replies

  • Try below code

    Function CountUnique(range1 As Range, range2 As Range)
    Dim col As Collection
    Set col = New Collection
    Dim rng As Range, cell As Range
    Set rng = Union(range1, range2)
    On Error Resume Next
    For Each cell In rng
       If cell.Value <> "" Then col.Add cell.Value, cell.Value
    CountUnique = col.Count
    End Function
    Sub test()
        Dim rng1  As Range
        Set rng1 = Range("A1:A5")
        Dim rng2  As Range
        Set rng2 = Range("B1:B5")
      MsgBox CountUnique(rng1, rng2)
    End Sub

    Thursday, May 23, 2013 9:14 PM
  • Sorry, I don't know the Union function, how does that work?

    and wouldn't this sum the data rather than count it?

    Thursday, May 23, 2013 9:33 PM
  • Try running the code and see if its giving the expected result for you.

    Thursday, May 23, 2013 9:37 PM
  • No, it is still throwing a #VALUE! error.

    Thursday, May 23, 2013 9:44 PM
  • Its working for me :)
    Thursday, May 23, 2013 9:45 PM
  • If your UDF is returning a #Value error it's because an error has occurred in the function, in this case most likely due to attempting to compare cell values that cannot be compared, eg one of the cells in one of the input ranges itself is an error. Just for testing include you could include something like this (though obviously don't leave the Stop/Resume in)

    ' first line of code (under the variable declarations)
    On Error GoTo errH
    ' code
    CountUnique = TheCount
    Exit Function
         ' press F8 to step to the error
    End Function

    CountUnique seems to mean the opposite of "to see what recurrence there is in multiple data sets", so I'm not sure what the objective is. If your two ranges are A1:A5 and B1:B5 and the 2x5 cells all include the same value, your function will return 25.

    Peter Thornton

    Friday, May 24, 2013 10:56 AM