Counting unique items (2002)

Topic

New Reply

I can use Advanced Filter to copy out the unique items of a column and then count them. However, is there a function that I can use directly to count how many unique items there are? Thanks, Andy.

Reply | Quote

Replies

Try Chip Pearson’s page Duplicate And Unique Items In Lists

Steve

Reply | Quote

You can use an array formula (ctrl+shift+enter to confirm instead of just enter) to do a simple unique count :

{=SUM(1/COUNTIF(A1:A100,A1:A100))}

Where A1:A100 is your range (blanks are not allowed)

Reply | Quote

The following code starts with your list of items (Source) then creates a new list of Unique
items (Uniques) — If you dont want the unique list just delete the code line that creates it:

The msgbox will then give you the total of unique items.

Sub CreateUniqueList()
””””””””””””””””””””””’
‘Requires a reference to MS Scripting Runtime
‘In the VBA Editor, click on
‘Tools>References>Microsoft Scripting Runtime
”””””””””””””””””””””””

Dim Dict As Scripting.Dictionary
Dim i As Integer
Dim oCell As Range
Dim Source As Range
Dim Uniques As Range

‘ ‘ Adjust ranges as needed
Set Source = Range(“A:A”)
Set Uniques = Range(“B:B”)

Set Dict = New Dictionary
i = 1
With Dict
For Each oCell In Source
If Not .Exists(oCell.Value) Then
If Not oCell.Value = “” Then
.Add Key:=oCell.Value, Item:=i
‘ Optional — Create a unique list
Uniques(i) = oCell.Value
‘ increment the counter
i = i + 1

End If
End If
Next oCell
End With
‘Optional Msg
MsgBox “There are ” & i & ” unique items in your list.”

Shutdown:
Set Dict = Nothing
Set oCell as Nothing
Set Source as Nothing
Set Uniques as Nothing

End Sub

Reply | Quote

Try,

=SUMPRODUCT((A1:A100″”)/COUNTIF(A1:A100,A1:A100&””))

or with the Morefunc addin

=COUNTDIFF(A1:A100)

Reply | Quote

Version 3.9 (the latest at this moment) of the morefunc.xll add-in would allow:

=COUNTDIFF(A1:A100,,””)

which behaves like the SumProduct version vis-a-vis empty cells and formula-blanks.

Reply | Quote

I think the credit for this goes to Hans, however, if it is someone else, I apologize
This function will count unique items in a list:

Function CountUniqueValues(InputRange As Range) As Long
Dim cl As Range, UniqueValues As New Collection
Application.Volatile
On Error Resume Next ‘ ignore any errors
For Each cl In InputRange
UniqueValues.Add cl.Value, CStr(cl.Value) ‘ add the unique item
Next cl
On Error GoTo 0
CountUniqueValues = UniqueValues.Count
End Function

You would then use CountUniqueValues(a1:a50) or whatever range

Reply | Quote

I don’t think the credit for this should go to any individual person, these ideas float around…

Reply | Quote

John Walkenbach attributes this tip of using the collection to hold unique items to J.G. Hussey, published in “Visual Basic Programmer’s Journal”, though I do not know if this was the “first time” it was used…

Steve

Reply | Quote

Thanks!

Reply | Quote