Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Counting unique items (2002)

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

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

  5. #5
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting unique items (2002)

    Try,

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

    or with the Morefunc addin

    =COUNTDIFF(A1:A100)

  6. #6
    New Lounger
    Join Date
    Feb 2004
    Location
    Findlay, Ohio, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting unique items (2002)

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

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting unique items (2002)

    Thanks!

  10. #10
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Microsoft MVP - Excel

Posting Permissions

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