Results 1 to 15 of 15
Thread: Unique Values in a Column (2000)

20030701, 09:37 #1
 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)

20030701, 09:40 #2
 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 controlshiftenter in stead of enter.Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20030701, 09:48 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20030701, 09:52 #4
 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 ?

20030701, 10:01 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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)

20030701, 10:51 #6
 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.

20030701, 10:57 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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>

20030701, 10:59 #8
 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 ctrlshiftenter?
Press <F2> fir edit mode and confirm with ctrlshiftenter
Steve

20030701, 11:06 #9
 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>

20030701, 11:30 #10
 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 (nonblank) 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

20030702, 21:21 #11
 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 arrayentered. It's an elaboration of David Hager's
=SUM(1/COUNTIF(Range,Range))
in order to cope with empty cells and formulablanks [ blanks generated by formulas such as =IF(D1,1,"") ]
The formula in [2] uses a function from Longre's morefunc addin. It's fast and normally entered. It counts the formulablanks as a separate entity.
The one in [3], again normallyentered, is due to Harlan Grove. Like [1], it can cope with empty cells and formulablanks, that is, it treats them as alike.Microsoft MVP  Excel

20030705, 14:57 #12
 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.

20030705, 17:58 #13
 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

20030705, 19:08 #14
 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

20030705, 22:48 #15
 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