Unique Values in a Column (2000)

Topic

New Reply

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)

Reply | Quote

Replies

This famous array formula does that:

=SUM(1/COUNTIF(A1:A10,A1:A10))
Hit control-shift-enter in stead of enter.

Reply | Quote

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.

Reply | Quote

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 ?

Reply | Quote

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)

Reply | Quote

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.

Reply | Quote

Strange, the difference is too large to ascribe to rounding errors. It works without rounding errors for quite large ranges on my system

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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:

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

Steve

Reply | Quote

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

With the following formula in some convenient cell

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

and data only in the first 10 rows of column A as follows:

0
a
s
d
f
g
0
f
0
0

I get a value of 253.5

Odd!

Ian.

Reply | Quote

(Edited by HansV to activate URL – see Help 19)

See

post 271039

Reply | Quote

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

As I mentioned in post 270357

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

Should work fine even with the zeroes.

Steve

Reply | Quote

Thanks!

Ian

Reply | Quote

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.

Reply | Quote