Thursday, October 13, 2011

How to remove duplicates in LibreOffice Calc

If you have a column or a table and you want to remove duplicate entries do the following (in LibreOffice Calc)

Select the range you want to filter
Data/Filter/Standard Filter
Name the column that you want to filter
Select a condition that is always true, like field1 = Not empty
Click on the button More Options, select Remove Duplicate, select Copy to and put the location of an empty cell
The whole selected range will be copied without duplicates at that new location.
Posted by at
Labels: ,

31 comments:

  1. Thanks! This worked very well. On my LibreOffice (Macintosh version 3.3.2) the option is not "Remove Duplicate", but the more cryptic "No Duplicate".

    ReplyDelete

  2. I'd say it's worth adding a bit of detail:

    when you select the data initially, just select the column you wish to filter by (you do not need to select ALL the data).

    ReplyDelete

  3. hmm. it works for me only with maximum of up to 17 000 records... Any idea why?

    ReplyDelete

  4. same here, it was a 81000 rows sheet, but it only returned 16000, I know for sure that there were not 65000 duplicates.

    ReplyDelete

  5. Just did the same thing, and the number of results is actually 16384\, which is 2^14, so I suspect it's a bug somewhere, where there is a loop that maxes out at 16 bit values.

    ReplyDelete

  6. Hello! Do you have any idea how could I check for and remove duplicate words in text cells in LibreOffice?

    ReplyDelete

  7. ... to be more exact: LibreOffice Calc.

    ReplyDelete

  9. please help, if it is possible? i'm looking for highlite duplicates in libre office calc, I know it is simple in Excell-Conditional fotmatting/higlite duplicates, but i can't find similar function in Libre office calc.

    ReplyDelete

  10. I couldn't get this to work at first. I did some experimentation, and the source of the trouble was this step:
    "Select a condition that is always true, like field1 = Not empty".

    That didn't work for me. Instead, I found success when I used this: "field" "does not end with" "!!!!!!". I don't know why Milo's suggestion didn't work...maybe it's a bug with my version (4.0.4 on Win7).

    Also, one clarification: If you have a spreadsheet, and your duplicates are in one column, say "B", be sure to highlight only column B...not all the columns when you follow the other instructions. This contrasts with MS Excel, where you highlight all the columns, and you pick the duplicate column from the wizard.

    Additional useful tip: if you want to check for duplicates in multiple columns: Say you have a table of customers, and you want to only delete identical customers with the same address and phone number, then you would use the Concatenate function to combine those three columns into a new column. Then, you eliminate the duplicates on that column. (You might have to replace the formulas with values using a paste special=>text...I can't remember if that step was required).

    ReplyDelete
    Replies

    1. This comment has been removed by the author.

      Delete

    2. Paul thank you I had the same problem on Win 8 (Version: 5.0.3.2 (x64))
      your way with
      "field" "does not end with" "!!!!!!"
      and
      highlight only column B...not all the columns

      Delete

    3. I'd like to add to this: if you have multiple columns and are only filtering on one column, don't select the option to copy to a new range. It will only copy that column and exclude the rest of the data. Leave it deselected and it will filter the information in place.

      Delete

  11. Thank you very much - helped me a lot

    ReplyDelete

  12. You are a life saver. Thank you. I owe you a beer when we meet next time. ;)

    ReplyDelete

  13. Thanks for sharing this informative post! Those who work in data entry services that use the LibreOffice application will surely find this helpful. Cheers!

    ReplyDelete
  15. Replies

    1. This comment has been removed by the author.

      Delete

  16. Thx for sharing about, How to remove duplicates in LibreOffice Calc,,,,
    bali luxury villas

    ReplyDelete

  21. This comment has been removed by the author.

    ReplyDelete

  23. Perfect! Thank you.

    http://rentabilidadetesourodireto.com.br

    ReplyDelete

  24. I'm glad to see this post. By the way, you may be interested in replica ray bans.

    ReplyDelete

  25. You have some Duplicate Files on your computer which hampers your memory space.
    Just use "DuplicateFilesDeleter" software.
    If you use this software you will be get comfortable.

    ReplyDelete

  26. Thanks for this. Inspired me but not the problem I'm trying to solve. What I want is to finish up with only the results that appear only once in my first list i.e. I have this list

    aaa
    aaa
    bbb
    bbb
    aaa
    bbb
    zzz
    xxx
    yyy

    and what I want is only

    zzz
    xxx
    yyy

    Does any one have an answer?

    Rob

    ReplyDelete

Subscribe to: Post Comments (Atom)