88

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,630
  • 7
  • 28
  • 41
  • Dennis's answer below is much simpler than the accepted answer – Chanoch Oct 15 '17 at 21:46
  • See the closed "Bug 103105 - Feature Request: Please implement a unique() function to return unique values from a range" https://bugs.documentfoundation.org/show_bug.cgi?id=103105 . – Robert Pollak Sep 01 '21 at 08:52

4 Answers4

143

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,531
  • 2
  • 9
  • 2
  • 1
    fast and simple... sweet!! ;) – AgelessEssence Aug 10 '13 at 00:45
  • 1
    nice and effective :) – Anwar Nov 11 '15 at 06:11
  • 4
    This answer is pertinently wrong for the question posed! It does not get *distinct* values, it instead **removes all duplicate values** , the result of a list populated with {1,2,2,3,4} would be {1,3,4}. The unique value 2 is missing! The result should be {1,2,3,4}. – Joeppie May 16 '18 at 08:26
  • 1
    You probably have left column name defined instead of ‘-none-‘. – Dennis May 17 '18 at 09:27
  • Nice one Dennis that worked perfectly for me. IDK wot @Joeppie is on about, maybe he didn't follow the instructions, like you said ¯\_(ツ)_/¯ – Rick Davies Feb 04 '20 at 04:52
  • The use of standard filter will collapse un-matching values or in this case duplicate values so they no loner appear. But they aren't lost (check the row numbers). It's not obvious, however, how to "un-collapse" those values: the answer is to go and do the filter again, but instead of Step 3 you delete the entry for the filtered value. – Keith Tyler May 02 '23 at 02:43
24

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
  • 197
  • 3
  • 13
W_Whalley
  • 3,422
  • 1
  • 18
  • 17
  • 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 had a range of values with date format. `<> " "` worked well, while `>" "` did not. – sancho.s ReinstateMonicaCellio Jul 12 '15 at 12:38
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,254
  • 14
  • 11
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
  • 3