different values in one column

Is there a way to get all different values in one column in libreoffice-calc?
If I have a sheet looking like that:

column1 column2 column3
A       B       C
A       B       C
A       B       C
A       D       C
A       B       C
A       B       C

I'd like to know how can I find out that column2 has 2 different values, and that those 2 values are B and D.
thanx

Solution 1:

Counting distinct values

AFAIK there's still no built-in formula to count distinct values in a range. But there are different formulas around that do the same. I've tested two formulas working fine with your example data in LibreOffice 3.5:

• The first is (courtesy Bigyan Bhar):

  =SUMPRODUCT((Data<>"")/(COUNTIF(Data;Data)+(Data="")))

• The second, more complex one, is an array formula, so you need to hit CTRL+SHIFT+ENTER after you entered it (courtesy David Chapman):

  =SUM(IF(FREQUENCY(IF(LEN(Data)>0;MATCH(Data;Data;0);"");IF(LEN(Data)>0;MATCH(Data;Data;0);""))>0;1))

each with "Data" replaced by the range to evaluate.

Listing distinct values

To list distinct values, just define a filter on the input range, excluding duplicates:

(There's currently a bug in libreoffice preventing the user from disabling the "Range contains column labels" checkbox, but it will be fixed in 3.5.2.)