Unique text values in Excel

Question

I am using an Excel formula for counting unique text values:

=SUM(IF(FREQUENCY(COUNTIF(Colors,"<"&Colors),COUNTIF(Colors,"<"&Colors)),1))

I got it from this SO post.

I wonder if it's possible to make a user defined function(UDF) for this formula...
I've tried some functions, but I've always failed till now... :-)

Using Evaluate on the string and concatenating in any variables. — QHarr, Oct 21 '18 at 09:36

score 1 · Accepted Answer · answered Oct 22 '18 at 21:25

1

You can use a Dictionary to track everything incoming from a range, then just dump they keys of the dictionary back out as a string:

Public Function GetDistinct(inRange As Range)
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")

    'collect everything into the dictionary
    Dim rngCell As Range
    For Each rngCell In inRange.Cells
        If rngCell.Value <> "" And Not dict.Exists(rngCell.Value) Then
            dict.Add rngCell.Value, 1
        End If
    Next rngCell

    'The keys of a dictionary are an array
    'So we can just Join() them with a ", "
    GetDistinct = Join(dict.Keys, ", ")
End Function

In practice:

answered Oct 22 '18 at 21:25

JNevill

46,980
4
38
63

Thank you very much @JNevill ! That code works good! I Just changed the last part for: `GetDistinct= dict.Count` - to get the sum of unique value. I don't know if that's the right way, but it works – MrZH6 Oct 23 '18 at 09:48
Oh! Right... the sum. I missed that part and went for the more difficult answer. I'm glad it worked out for you! – JNevill Oct 23 '18 at 12:41

score 0 · Answer 2 · answered Nov 12 '18 at 20:18

I just want to add that I stumbled across a function regarding this matter. Using a Collection

It goes like this:

Function unique_values(rng As Range) As Double 
Dim unique As New
Collection Dim cl As Range Dim i As Integer

Set rng = Selection On Error Resume Next
     For Each cl In rng
         unique.Add cl.Value, CStr(cl.Value)
     Next cl On Error GoTo 0

unique_values = unique.Count

End Function

Just in case someone is interested. :-)

Unique text values in Excel

2 Answers2