Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

up vote 7 down vote accepted

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:

unique filter

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

share|improve this answer
    
thx a lot, the Listing distinct values part of your answer is exactly what I needed –  OSdave Mar 22 '12 at 16:28
add comment

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.

share|improve this answer
    
all values are in separated cells. –  OSdave Mar 21 '12 at 9:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.