44

I have gnumeric and libreoffice calc. How to find duplicate rows? if I could use libreoffice, it's better, but anything is good for me.

I have the latest stable version of both software

Improve this question
3

5 Answers 5

Reset to default
39

This a way you can hide and then remove duplicate rows (or 'records') in Libreoffice Calc

  • Go to the worksheet that has the duplicate entries, and select 'Data' > 'Filter' > 'Standard Filter' (it seems to automatically select the data in the sheet that it will filter, you may want to do this manually beforehand).
    Then in the box that pops up, select the column (Field - entering multiple 'Fields' may be possible), and as the value(s) enter 'Not Empty'.
    Then under the 'Options' pop-down menu, select 'No duplications' - you may want to select other options like 'Case-sensitive', 'Copy results to', etc as well. enter image description here

  • It should then filter the data, and hide any duplicate records. You can copy this to a new worksheet if you want to have the dataset without duplicates (instead of the dupes just being hidden).

    You can reset the filter if necessary by going to 'Data' > 'Filter' > 'Reset Filter'

    enter image description here

This worked with Libreoffice 4.2.5.2 The help page for 'Standard Filter' is here.

Improve this answer
1
  • This kind of works, with a workaround, but it's not really what was asked for. He asked specifically to find the duplicates.
    – Stefan Teunissen
    Jul 8, 2022 at 13:15
29

In order to find duplicate rows and their respective counts on MS Excel or LibreOffice you can use following formula:

=COUNTIF(A:A, "=" & A2)

It will provide count of duplicate records in the cell formula is applied on.

Explanation of formula:

  1. A:A is the range in which duplicate comparison will execute.
  2. "=" is the operator to compare records for duplicacy.
  3. & A2 is the value which will be compared in the range i.e; A:A (Column A)

You can use filter to check duplicate records with X no. of duplicacy.

Check screenshot for application of this formula:

Screenshot

Improve this answer
1
  • 1
    Thank you, this helps a lot ... let's upvote this answer !
    – Dan Ortega
    Jul 8, 2019 at 21:28
15

To detect the duplicates in a column there is a solution on www.techrepublic.com. It is written for MS Excel, but it works as well in gnumeric and LibreOffice Calc.

Its mainly about this formula:

=if(countif(e$4:e$9;e4)>1;'yes';'no')

See the screenshot:

enter image description here

Improve this answer
3
  • 1
    Welcome to Ask Ubuntu! I recommend editing this answer to expand it with a summary or restatement of the important information from the linked page, so this answer is self contained. (That is considered preferable to an answer that only makes sense if people read the externally linked page.)
    – Eliah Kagan
    Jul 26, 2014 at 15:44
  • This doesn't work for me in Libreoffice calc :(
    – Mr. T
    Jun 18, 2020 at 4:23
  • @Mr.T Can U tell me more about the issue ? Can U provide your file ?
    – Manu
    Sep 2, 2020 at 21:24
2

Use conditional formatting to highlight duplicates in any range/column/cells etc. Go to Format tab - Conditional - Condition. This opens a dialog box and select duplicates and set the Accent to whatever you want.

Improve this answer
1

To find entire duplicate rows,

  1. concatenate the whole row and
  2. then use the countif (mentioned elsewhere) on that :-)
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .