How do you Count the number of unique values in a list ?
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 26

Thread: How do you Count the number of unique values in a list ?

  1. #1
    Guest

    Default

     
    I would like to know how many unique values I have in a given list / range.

    Is there a formula to return this number. The data is generally alphanumeric.

    Thanks
    Sean

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    United Kingdom
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I assume by unique numbers, you merely want to count how many different numbers appear in the list?

    If so, a Pivot Table would do it quite easily.

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    232
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    you could try sorting your list and using the Data-> Subtotal function.

    This groups up your data and subtotals the group.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,752
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-05 04:53, Anonymous wrote:
    I would like to know how many unique values I have in a given list / range.

    Is there a formula to return this number. The data is generally alphanumeric.

    Thanks
    Sean
    You can also use the following formula to obtain the desired count of uniques items:

    =IF(LEN(A1:A6),SUMPRODUCT(1/COUNTIF(A1:A6,A1:A6)))

  5. #5

    Join Date
    Feb 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    In addition to the responses already supplied, here's a UDF :-

    Function Uniques(Rng As Range) As Long
    Dim cell As Range, UniqueValues As New Collection
    Application.Volatile
    On Error Resume Next
    For Each cell In Rng
    UniqueValues.Add cell.Value, CStr(cell.Value)
    Next cell
    On Error GoTo 0
    Uniques = UniqueValues.Count
    End Function

  6. #6
    MrExcel MVP Tazguy37's Avatar
    Join Date
    May 2004
    Location
    Aurora, Colorado
    Posts
    4,237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do you Count the number of unique values in a list ?

    What if you want to know not only the count of each unique item, but the items themselves? There could be small differences between the items, which you would want accounted for.



    P.S. I love this forum! I've thought about posting many times, only to find the exact solution I needed! Kudos to all!
    Todd Seward
    Building Successful Technical Solutions

    Learn something new every day!


    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  7. #7
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,445
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do you Count the number of unique values in a list ?

    Quote Originally Posted by Tazguy37
    What if you want to know not only the count of each unique item, but the items themselves? There could be small differences between the items, which you would want accounted for.



    P.S. I love this forum! I've thought about posting many times, only to find the exact solution I needed! Kudos to all!
    For a list of unique items, see,

    http://216.92.17.166/board2/viewtopic.php?t=8659

    Aladin's revised method

  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do you Count the number of unique values in a list ?

    Hi Taz:

    Welcome to MrExcel Board!

    Do you mean to say you would like to extract all those items that are unique (meaning suppressing the duplicates)? -- if so then the UNIQUEVALUES function from the Morefunc add-in should do the job.

    I am sorry ... I did not understand the part of your statement ...
    There could be small differences between the items, which you would want accounted for.
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  9. #9
    MrExcel MVP Tazguy37's Avatar
    Join Date
    May 2004
    Location
    Aurora, Colorado
    Posts
    4,237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How do you Count the number of unique values in a list ?

    Quote Originally Posted by Yogi Anand
    Hi Taz:

    Welcome to MrExcel Board!

    Do you mean to say you would like to extract all those items that are unique (meaning suppressing the duplicates)? -- if so then the UNIQUEVALUES function from the Morefunc add-in should do the job.

    I am sorry ... I did not understand the part of your statement ...
    There could be small differences between the items, which you would want accounted for.
    Thanks for the reply, Yogi! Yes, UNIQUEVALUES does the job just great, however -- not everyone that sees my work likes to install add-ins on their PC (or knows how, truth be told ).

    In the 2nd part of my post, I just meant that you'd want to count all the unique items, not just ones that are "close." You can ignore that part...no worries.

    Thanks for all the help!
    Todd Seward
    Building Successful Technical Solutions

    Learn something new every day!


    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,752
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How do you Count the number of unique values in a list ?

      
    Quote Originally Posted by Tazguy37
    ...
    Yes, UNIQUEVALUES does the job just great, however -- not everyone that sees my work likes to install add-ins on their PC (or knows how, truth be told ).
    My contrib in

    http://216.92.17.166/board2/viewtopi...light=distinct

    describes a fast formula system to extract unique/distinct items from a range.

    Method 1 (Brian refers to) in:

    http://216.92.17.166/board2/viewtopi...r=asc&start=20

    sorts and extracts such a list.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com