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. AnonymousJanuary 18, 2012 at 10:57 AM

    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. TalkSolarPanels.co.ukFebruary 29, 2012 at 2:01 AM

    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. cobrikApril 3, 2012 at 5:16 AM

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

    ReplyDelete
  4. IApril 11, 2012 at 5:39 PM

    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. JohnJuly 18, 2012 at 9:11 PM

    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. Regele IONESCUOctober 24, 2012 at 1:58 AM

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

    ReplyDelete
  7. Regele IONESCUOctober 24, 2012 at 1:59 AM

    ... to be more exact: LibreOffice Calc.

    ReplyDelete
  8. SudhakarNovember 28, 2012 at 7:37 AM

    Perfect! Thanks

    ReplyDelete
  9. Irakli GaruchavaFebruary 4, 2013 at 10:31 PM

    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. PaulFebruary 11, 2014 at 9:12 PM

    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. UnknownDecember 22, 2015 at 6:56 AM

      This comment has been removed by the author.

      Delete
    2. Artyom MuravyovDecember 22, 2015 at 6:57 AM

      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. AnonymousJanuary 6, 2016 at 1:41 PM

      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. mirqoApril 13, 2014 at 12:29 AM

    Thank you very much - helped me a lot

    ReplyDelete
  12. Nebojsa ManojlovicMay 19, 2014 at 9:21 AM

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

    ReplyDelete
  13. AnonymousJuly 9, 2014 at 10:42 PM

    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
  14. ABCDAKDNKADKOctober 7, 2014 at 2:21 PM

    It helped me a lot, thanks!

    ReplyDelete
  15. deric santosoOctober 28, 2014 at 9:01 PM

    wow keren banget gan

    Dana Cepat

    ReplyDelete
  16. sabian rakhaNovember 17, 2014 at 8:07 PM

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

    ReplyDelete
  17. Tonny MarezcoJanuary 15, 2015 at 8:00 PM

    Nice share and keep posting Sir ^.^
    paket meeting di bali

    ReplyDelete
  18. Tristan BaileyMarch 5, 2015 at 4:44 AM

    Thanks for the tip

    ReplyDelete
  19. ArmanOctober 20, 2015 at 4:08 PM

    Excellent thanks

    ReplyDelete
  20. Gaurav VichareMarch 22, 2016 at 4:16 AM

    Thank You.. Working :)

    ReplyDelete
  21. Brenda PalmeriMay 26, 2016 at 5:38 AM

    This comment has been removed by the author.

    ReplyDelete
  22. zamaanJune 3, 2016 at 1:09 AM

    helped thank you

    ReplyDelete
  23. Gialuca GhisleniSeptember 5, 2016 at 1:56 PM

    Perfect! Thank you.

    http://rentabilidadetesourodireto.com.br

    ReplyDelete
  24. AnonymousSeptember 19, 2016 at 12:14 AM

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

    ReplyDelete
  25. Kevin BrownJanuary 21, 2017 at 9:10 AM

    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. Robert EmmetOctober 13, 2017 at 11:50 PM

    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)