Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unique Values in a Column (2000)

    Is there an easy way to determine the number of unique bits of text in a column (or part of an area) without having a separate list of what *could* be in the column. ?

    For example:

    Column A


    Apple
    Apple
    Apple
    Pear
    Apple
    Pear
    Grape
    Grape
    Grape
    Apple
    Pear
    Apple

    Answer is 3 (Apples, Pears, and Grapes)

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique Values in a Column (2000)

    This famous array formula does that:

    =SUM(1/COUNTIF(A1:A10,A1:A10))
    Hit control-shift-enter in stead of enter.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Unique Values in a Column (2000)

    Steve,

    Let's say that your data are in range A1:A37. Enter the following formula in the cell that should contain the number of unique items:

    =SUM(1/COUNTIF(A1:A37,A1:A37))

    This is an array formula. i.e. you must confirm it with Ctrl+Shift+Enter instead of just Enter.

    Remark: there should be no gaps (empty cells) in the range.

  4. #4
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique Values in a Column (2000)

    Ahhh, there are empty cells in the range.... Is there an easy way of getting around this problem other than copying them all to another range of cells ?

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

    Re: Unique Values in a Column (2000)

    Try

    =SUM(IF(COUNTIF(A1:A37,A1:A37)=0,0,1/COUNTIF(A1:A37,A1:A37)))

    Again, entered as an array formula (Ctrl+Shift+Enter)

  6. #6
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique Values in a Column (2000)

    It works Hans.. kind of....


    =SUM(IF(COUNTIF(AH22:AH1000,AH22:AH1000)=0,0,1/COUNTIF(AH22:AH1000,AH22:AH1000)))

    And the result is : 2.0553047404




    There are two unique values in the range. experimenting atm with more reveals the first number is always correct.. so can just Int it or
    round it etc...

    Many thanks.

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

    Re: Unique Values in a Column (2000)

    Strange, the difference is too large to ascribe to rounding errors. It works without rounding errors for quite large ranges on my system <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  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: Unique Values in a Column (2000)

    Did you remember to confirm with ctrl-shift-enter?
    Press <F2> fir edit mode and confirm with ctrl-shift-enter
    Steve

  9. #9
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique Values in a Column (2000)

    Steve, yes it puts curly brackets around the whole formula..

    Hans, yea its a bit strange, but i tested it with various amounts that i would encounter, and if do -int(cell) then its giving me the number i expect.


    You only get half a beer for that one though Hans cos its not perfect <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  10. #10
    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: Unique Values in a Column (2000)

    Do you have a "Null string" ("")in your data set (either a formula that returns a null string, or even a cell (non-blank) that STARTS with a single quote (') so it looks BLANK but isn't, in addition to truly blank cells?

    This will "screw up the values" since Hans "fixed the div/0 error of the 1/0, BUT the null string will count all the BLANK CELLS so its count is off!

    Try:
    <pre>=SUM(IF(LEN(AH22:AH1000)=0,0,1/COUNTIF(AH22:AH1000,AH22:AH1000)))</pre>


    Steve

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

    Re: Unique Values in a Column (2000)

    1]

    =SUM(IF(A2:A13<>"",1/COUNTIF(A2:A13,A2:A13)))

    =SUM(IF(LEN(A2:A13),1/COUNTIF(A2:A13,A2:A13)))

    2]

    =COUNDIFF(A2:A13)

    3]

    =SUMPRODUCT((A2:A13<>"")/COUNTIF(A2:A13,A2:A13&""))

    The formula in [1] must be array-entered. It's an elaboration of David Hager's

    =SUM(1/COUNTIF(Range,Range))

    in order to cope with empty cells and formula-blanks [ blanks generated by formulas such as =IF(D1,1,"") ]

    The formula in [2] uses a function from Longre's morefunc add-in. It's fast and normally entered. It counts the formula-blanks as a separate entity.

    The one in [3], again normally-entered, is due to Harlan Grove. Like [1], it can cope with empty cells and formula-blanks, that is, it treats them as alike.
    Microsoft MVP - Excel

  12. #12
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique Values in a Column (2000)

    Not sure what's happening, but zeros seem to behave oddly.

    With the following formula in some convenient cell
    <pre>=SUM(IF(COUNTIF(A1:A1000,A1:A1000)=0,0,1/COUNTIF(A1:A1000,A1:A1000)))</pre>

    and data only in the first 10 rows of column A as follows:<pre>0
    a
    s
    d
    f
    g
    0
    f
    0
    0

    </pre>


    I get a value of 253.5

    Odd!

    Ian.

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

    Re: Unique Values in a Column (2000)

    (Edited by HansV to activate URL - see <!help=19>Help 19<!/help>)

    See

    <post#=271039>post 271039</post#>
    Microsoft MVP - Excel

  14. #14
    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: Unique Values in a Column (2000)

    All the empty cells match the Zero as they also match the the null strings.

    As I mentioned in <post#=270357>post 270357</post#>

    <pre>=SUM(IF(LEN(A1:A1000)=0,0,1/COUNTIF(A1:A1000,A1:A1000)))</pre>


    Should work fine even with the zeroes.

    Steve

  15. #15
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique Values in a Column (2000)

    Thanks!

    Ian

Posting Permissions

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