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?!?
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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.