90

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

Basically, this question:

How do I get the distinct/unique values in a column in Excel?

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

I'm using Open Office 3.2

Community
  • 1
David Oneill
  • 2,678

5 Answers5

145

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.

Dennis
  • 1,551
25

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 by clicking on the column header.,
  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 >" ". Use any unused cells in your spreadsheet - these are just temporary inputs which are needed to apply the filter.
  3. Select the header of your data column to highlight the whole 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.

JJD
  • 199
W_Whalley
  • 3,522
  • 1
  • 21
  • 18
1

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.

Jeremy Sturdivant
  • 2,284
0

Pressing Alt + Down while selecting a cell in the column produced a dropdown list of distinct values in that column for me.

egor83
  • 103
0

A procedure using a standard filter is now in the Libreoffice online help system. It works very well. https://help.libreoffice.org/24.8/en-US/text/scalc/guide/remove_duplicates.html?DbPAR=CALC

Note that at some future date the online help topic may be more up to date than this copy of its current text. RTFH.

Removing Duplicate Values Consider a single column with values where some may be duplicates. To remove duplicate values and obtain only the unique entries in the column:

Select the range of values from which duplicate values are to be removed. The values have to be arranged in a single column.

Go to Data - More Filters - Standard Filter. This opens the Standard Filter dialog.

In Field Name make sure that the selected column is the column where the values are stored. If a single column is selected this field will be set automatically.

In Condition choose the option = (equals sign), which is the default option.

In Value choose the option Not Empty.

Click Options and select No duplications. If the first value is the column header check Range contains column labels.

Check Copy results and use the input box below it to inform a cell address where the unique entries are to be entered.

Click OK. The unique values in the range will be entered starting with the cell selected in the previous step.

note The No duplications option is case sensitive. Therefore, values "A" and "a" are each considered as unique values.

Bruce Nevin
  • 1