different values in one column

Question

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

Answer 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.)

Answer 2

This basically libreoffice related question so it can be better if you can post it to libreoffice forums. and as far as having two different values in one cell is concerned (that is what I have understood) I think you can have two different adjacent cells to store value and then merge above two cells to store single heading.