1. Counting unique items (2002)

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.

2. Re: Counting unique items (2002)

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

Steve

3. Re: Counting unique items (2002)

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)

4. Re: Counting unique items (2002)

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
' 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

5. Re: Counting unique items (2002)

Try,

=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))

=COUNTDIFF(A1:A100)

6. Re: Counting unique items (2002)

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
Next cl
On Error GoTo 0
CountUniqueValues = UniqueValues.Count
End Function

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

7. Re: Counting unique items (2002)

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

8. Re: Counting unique items (2002)

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

Thanks!

10. Re: Counting unique items (2002)

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.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•