Count all unique entries in a column (mission impossible?!)

[enfantter]

Hi,

This might not be the appropriate place to put this question, but here goes,

In a column A i have a long range of entries which is a userid. The same user has mulitple entries. I wanna (preferable with an excel function) all unique entries in the column. On the web i found several page describing something like this, e.g.

http://www.theworldofoffice.com/excel-tips-tricks/count-all-unique-excel-cells-or-entries-with-this-formula/

but with this formula i cant put the whole column (A:A) as a reference.

Does any of you brilliant people know how to solve this (the calculation time is huge if i put all rows (e.g. A1:A65356)) or is this mission impossible?!?!

/Enfant Terrible

[stanleydgrom]

enfantter,

Try:

Count unique items in a column

=SUMPRODUCT(($A$1:$A$65356<>"")/COUNTIF($A$1:$A$65356,$A$1:$A$65356&""))

Or, for the entire column:
=SUMPRODUCT(($A$1:$A$65536<>"")/COUNTIF($A$1:$A$65536,$A$1:$A$65536&""))


Have a great day,
Stan

[enfantter]

thnx for your reply !

It's definetly something like this i wanna do, but the calculation time from running through every row is long, so i was hoping that you could do this for the entire row in another way?!?

[GTO]

thnx for your reply !

It's definetly something like this i wanna do, but the calculation time from running through every row is long, so i was hoping that you could do this for the entire row in another way?!?

Greetings,

From your previous posts, I'm sure you meant 'entire column'. Maybe try a UDF?

In a Standard Module:

Option Explicit

Function COUNTU(CellRange As Range) As Long
Dim _
DIC As Object, _
aryVals As Variant, _
vntVal As Variant

aryVals = CellRange.Value
Set DIC = CreateObject("Scripting.Dictionary")
With DIC
.CompareMode = vbTextCompare
For Each vntVal In aryVals
If vntVal <> "" Then .Item(vntVal) = Empty
Next
COUNTU = .Count
End With
End Function


Entered in the worksheet like:


=COUNTU(A:A)


Does that help?

Mark

[mdmackillop]

You could also try filtering unique values using Advanced Filter

[enfantter]

@GTO it works fine !
just what i was looking for - i just hoped that i could do this with regular excel functions, but alternatively this is good.

[enfantter]

Is it possible to add a condition so this only counts unique entries for a specific month?!

column A is a column with date entries ..

[Bob Phillips]

Function CountUnique(rng As Range)
Dim Uniques As Collection
Dim cell As Range
Dim col As Long
Dim LastRow As Long
Dim i As Long

With rng.Parent

col = rng.Column
LastRow = .Cells(.Rows.Count, col).End(xlUp).Row
Set Uniques = New Collection
On Error Resume Next
For i = 1 To LastRow

Uniques.Add .Cells(i, col).Value, CStr(.Cells(i, col).Value)
Next i

CountUnique = Uniques.Count
End With
End Function

[GTO]

Greetings enfanntter,

At #7, I think you are saying that you wish to count the number of unique values in one column, wherein said unique values correlate to sister cells containing a date value within a specified month (presume year?).

If that is the case, could you post an example wb?

Mark

[enfantter]

@ mark

If think what you are saying is exactly what i want ...

[enfantter]

Sort of like a sumprodukt of the above countu ...
where vector 2 is dates ..
Nonsense?

[enfantter]

Ok - i guess i need to clarify on this one.

#4 works perfect for me when i want to count the unique entries in column A - thank you xld !

Now i wanna do something similiar - maybe i should have made a new post, but it is somewhat related. I wanna count the unique entries in column A, but only when the date in column B is within a certain month.

Hopes this clarifies a little :S

[Bob Phillips]

Function CountUnique(rng As Range, rngDate As Range, mth As Long)
Dim Uniques As Collection
Dim cell As Range
Dim col As Long
Dim LastRow As Long
Dim i As Long

With rng.Parent

col = rng.Column
LastRow = .Cells(.Rows.Count, col).End(xlUp).Row
Set Uniques = New Collection
For i = 1 To LastRow

If Month(.Cells(i, rngDate.Column).Value) = mth Then

On Error Resume Next
Uniques.Add .Cells(i, col).Value, CStr(.Cells(i, col).Value)
On Error GoTo 0
End If
Next i

CountUnique = Uniques.Count
End With
End Function