PDA

View Full Version : Count all unique entries in a column (mission impossible?!)



enfantter
10-16-2009, 10:16 AM
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
10-16-2009, 10:30 AM
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
10-16-2009, 10:39 AM
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
10-16-2009, 01:38 PM
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
10-17-2009, 03:22 AM
You could also try filtering unique values using Advanced Filter

enfantter
10-18-2009, 11:46 PM
@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
11-12-2009, 01:22 AM
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
11-12-2009, 02:18 AM
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
11-13-2009, 06:02 AM
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
11-16-2009, 02:24 AM
@ mark

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

enfantter
11-16-2009, 06:15 AM
Sort of like a sumprodukt of the above countu ...
where vector 2 is dates ..
Nonsense?

enfantter
11-16-2009, 07:37 AM
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
11-16-2009, 09:58 AM
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