Solved: counting unique in vba

[chungtinhlak]

I have about 30 different columns with names

Is there a way that I can just count how many unique names are in each column?

for example:

A

thaison
John
thaison
mark
john
thaison
john
john


this would come back as 3 because of 3 unique names.

thanks

[lucas]

Formula?
Count the unique entries If your list of unique entries is long, you can use the COUNTA worksheet function to count them. For example, the following formula counts the number of entries in column F, minus 1 for the column label:

=COUNTA($F:$F)-1

[lucas]

VBA?
Option Explicit
Private Sub CommandButton1_Click()
Dim oUniques As Collection
Dim cell As Range
Dim i As Long
Set oUniques = New Collection
On Error Resume Next
'start looking for unique items starting in cell A3
For Each cell In Range("a1:A1103")
oUniques.Add CStr(cell.Value), CStr(cell.Value)
Next
On Error GoTo 0
MsgBox oUniques.Count
End Sub

[mdmackillop]

From Help file
=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1))

[chungtinhlak]

thank you

[Zack Barresse]

Using Malcolm's formula in VBA...
dim x as variant, rng as range
set rng = range("a1:a10") 'needs to exclude blanks
x = Evaluate("=SUM(IF(FREQUENCY(MATCH(" & rng.address & "," & rng.address & _
",0),MATCH(" & rng.address & "," & rng.address & ",0))>0,1))")
HTH

[mdmackillop]

For completeness, here is the whole summary from the Help. Note that these are Array formulae.

Formula Description (Result)
=SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1)) Count the number of unique number values in cells A2:A10, but do not count blank cells or text values (4)
=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1)) Count the number of unique text and number values in cells B2:B10 (which must not contain blank cells) (7)
=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1)) Count the number of unique text and number values in cells A2:A10 , but do not count blank cells or text values (6)

[chungtinhlak]

Lucs,
I am working with you code, it works perfectly but I don't understand how it works, can you help me. in your comment you said start looking for unique items starting in cell A3, do you mean A1?

I guess the trick is CStr(cell.Value), CStr(cell.Value), but how does that work, I google CStr and that all I got was turn value into string. Please let me know how it works.


VBA?
Option Explicit
Private Sub CommandButton1_Click()
Dim oUniques As Collection
Dim cell As Range
Dim i As Long
Set oUniques = New Collection
On Error Resume Next
'start looking for unique items starting in cell A3
For Each cell In Range("a1:A1103")
oUniques.Add CStr(cell.Value), CStr(cell.Value)
Next
On Error GoTo 0
MsgBox oUniques.Count
End Sub

[lucas]

The comment was wrong. I changed the code before I posted it and forgot to change the comment.

The CStr changes any numbers to string to be evaluated. There are all kinds of collection objects but we create a new custom collection in this code.......

the first part of the line adds the string to the collection, after the comma comes the key followed by the expression as string.

type the code in your vbid with intellisense on to get the parts...

[Bob Phillips]

From Help file
=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1))


A non-array version

=SUMPRODUCT((A2:A20<>"")/COUNTIF(A2:A20,A2:A20&""))

[Bob Phillips]

From Help file
=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1))

You can simplify that a tad as well

=COUNT(IF(FREQUENCY(MATCH(A2:A10,A2:A10,0),MATCH(A2:A10,A2:A10,0)),1))

[chungtinhlak]

Thanks All, still have a couple questions here for Lucus.
so the first cell.value is to add it(as string) to our collection, the second cell.value is blurry to me. and how did it become unique?

thanks all for helping



The comment was wrong. I changed the code before I posted it and forgot to change the comment.

The CStr changes any numbers to string to be evaluated. There are all kinds of collection objects but we create a new custom collection in this code.......

the first part of the line adds the string to the collection, after the comma comes the key followed by the expression as string.

type the code in your vbid with intellisense on to get the parts...

[mdmackillop]

A Collection cannot have 2 identical keys. Adding a duplicate entry will cause an error (handled by the code) leaving you with a unique list.

[chungtinhlak]

ok, one more thing, in that case, then why do we have to add it twice?

thanks a lot

[Bob Phillips]

You don't add it twice, you add a key and the value associated with that key.

[chungtinhlak]

ok thanks

[Zack Barresse]

You can simplify that a tad as well..
That might be overstating it.. :devil2: