Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

How do I get a list of all distinct values from a column of values?

Basically, this question:

Excel: Get distinct values in column

but I need the answer for Open Office Calc instead of MS Excel.

I'm using Open Office 3.2

share|improve this question

3 Answers 3

up vote 8 down vote accepted

You can do this with the OpenOffice advanced filter (on the main menu..Data/Filter/Advanced Filter)

  1. Ensure your column of data has a title at the top, e.g. title and that the data is contiguous (no empty cells) or select the whole column including empty cells.,
  2. Create a filter criterion that would include all the data in your column, e.g. in cell D1 enter title; in cell D2 enter >" "
  3. Select the title cell of your data column, then choose Data/Filter/Advanced Filter.
  4. In the box that opens, for 'Read Filter Criteria from' choose both cells of your filter (e.g. D1 and D2)
  5. Click the 'More' button and check the 'No duplication' box. Optionally you can choose to copy the filtered data somewhere else. Click OK and distinct cells will be displayed.

Not very intuitive, but once you get the hang of it, it goes pretty fast.

share|improve this answer
    
THANKS!!! This is gold. I was looking for exactly this and Google pointed me here. I know how to use Advanced Filter to do the same thing in excel but I needed to figure out how to accomplish this in OpenOffice. You are exactly right with this, I hope your answer is accepted! –  gmale Feb 17 '11 at 21:20
    
A couple more things: this didn't work very well when formulas were in the cells. So I cut/paste to another column as text (via paste special) and then it worked fine. Also, for the criteria, I used <>"" instead of >" ", it seemed to work better with my data –  gmale Feb 17 '11 at 21:21

I found a more simple way to do that:

  1. Select the whole column

  2. Data > Filter > Standard Filter

  3. Change 'Field Name' to -none- , click on 'More options' and check on 'No duplication' box

That's it. You can copy and paste the filtered fields if you want contiguously numbered cells.

share|improve this answer
    
fast and simple... sweet!! ;) –  iim.hlk Aug 10 '13 at 0:45

You could try the procedure described in http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Removing_Duplicate_Rows to completely remove non-unique rows according to the column in question, or select rows based on the new column.

share|improve this answer

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.