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

• ### 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
Next

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
errH:
Stop
' press F8 to step to the error
Resume
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