counting the number of unique text strings in a column

[nutsorters]

I wonder if somebody could help me please.

I have a list of around 50000 names. I need to count how many are unique.

Is there an quick and easy way to do this?

Many thanks

 

[tlee]

rugila,

Sub uniques()
Dim n As Long, i As Long, a, x
n = [a65536].End(xlUp).Row
a = Range("A1:A" & n)
With CreateObject("Scripting.Dictionary")
For i = 1 To n
    If Not .exists(a(i, 1)) Then
        .Add a(i, 1), 1
    Else: .Item(a(i, 1)) = .Item(a(i, 1)) + 1
    End If
Next i
x = Application.Transpose(Array(.keys, .items))
[c1].Resize(.Count, 2) = x
End With
End Sub

This code is terrific! In my line of work I can use it for a ton of different things ... and it works so fast!

Please help me here as I am a bit of a novice (understatement). I would really appreciate it if you could walk me through the code and how it works. I've been trying to pick it apart and alter it just so I can understand it, but it stumps me.

 

[jindon]

Sub test()
Dim a, e, b(), n As Long
a = Range("a1").Resize(Range("a" & Rows.Count).End(xlUp).Row).Value
' store data in an array a to speed up the process
ReDim b(1 To UBound(a,1), 1 To 2)
' prepare output array
With CreateObject("Scripting.Dictionary")
' reference to Dictionary object (refer vb help for Dictionary object)
    .CompareMode = vbTextCompare
    ' Non case sensitive compare (vbBinaryCompare as default)
    For Each e In a
    ' loop though array a
        If Not IsEmpty(e) Then  ' when e (an element in array a) is not empty
            If Not .exists(e) Then '<- if e first appeared
                n = n + 1       '<- increase the counter
                b(n,1) = e      '<- input e in 1st dim of array (Unique word)
                b(n,2) = 1      '<- input 1 in 2nd dim of array (count of the word)
                .add e, n   '<- store e in Key, n(counter) in Item property of dictionary object
            Else  '<- if already appeared
                b(.item(e),2) = b(.item(e),2) + 1
                ' increase the count of the word in an array
            End If
        End If
    Next
End With
Range("c1").Resize(n,2).Value = b
End Sub

 

[mikerickson]

One variation of the spreadsheet idea above is, if your column is in A,

a helper column B1 filled down
=IF(COUNTIF($A$1:A1,A1)=1,ROW(),9.9E+99)

a column of the unique items in A
=INDEX(A:A,SMALL(B:B,ROW()))

the number of unique items in A.
=COUNTIF(B:B,"<"&9E+99)

 

[Aladin Akyurek]

Look here:

http://www.j-walk.com/ss/excel/usertips/tip061.htm

Andrew, have you used those formulas much? The reason I am asking is ...

I just did some testing on a single column of data with about 8000 rows, including blanks.

This formula
=SUMPRODUCT((A1:A8000<>"")/COUNTIF(A1:A8000,A1:A8000&""))
took about 30 seconds to come up with a result of 960 which I believe to be correct.

The second formula in the link you provided
=SUM(IF(COUNTIF(A1:A8000,A1:A8000)=0, "", 1/COUNTIF(A1:A8000,A1:A8000))) entered with CSE
took about 1.25 minutes and came up with a result of 1589.5
Apart from taking much longer and being incorrect, its hard to imagine any count of unique entries not being an integer!!

Do you have any comments on this?

{=SUM(IF(COUNTIF(A1:A8000,A1:A8000)=0, "", 1/COUNTIF(A1:A8000,A1:A8000)))}

is a provably poor re-write of:

{=SUM(IF(A1:A8000<>"",1/COUNTIF(A1:A8000,A1:A8000)))

for it doubles the costs associated with CountIf.

See for more (in self-citation mood)...

http://www.mrexcel.com/board2/viewtopic.php?t=37550&highlight=token
http://www.mrexcel.com/board2/viewtopic.php?t=17071&highlight=hager
http://www.mrexcel.com/board2/viewtopic.php?t=73502&highlight=grove

BTW, a formula with COUNTDIFF is the fastest approach.