Count Distinct Values

I’m sorry for the lack of posts over the last few months – what can I say, I’ve been busy. I had meant to continue the series on data access patterns from here and I put a revision of the code here, but I haven’t had the time to do much more. I may get back to it eventually, but in the meantime I thought I’d post something else which I’ve found dictionaries to be quite useful for.

The below is a function which gives a count of the distinct items in an array. As items are added, the dictionary will prevent duplication of key values – and the Count method gives the number of keys in the dictionary.

Public Function CountDistinct(ByRef vArr As Variant) As Variant

    Dim dict As Scripting.Dictionary
    Dim lVal As Long
    Set dict = New Scripting.Dictionary
    For lVal = LBound(vArr) To UBound(vArr)
        dict(vArr(lVal)) = vArr(lVal)
    Next lVal
    CountDistinct = dict.Count

End Function


A much simpler implementation is available in Python, using the set datatype:

>>> def CountDistinct(vals):
...   return len(set(vals))
>>> CountDistinct([1, 2, 3, 3, 4])

2 thoughts on “Count Distinct Values

  1. JP says:

    Welcome back, we all need a break from time to time.

    I like the CountDistinct function, but I would make it late bound instead so I don’t have to add a reference to the Scripting library.

    • geoffness says:

      Thanks JP 🙂

      Good point – ideally of course as Roy pointed out in an earlier comment, the Dictionary would be built in to VBA. But yeah late binding would be better.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s