Remove duplicate rows does not work in Calc

Question

I tried to remove duplicate rows of the same column in LibreOffice by using Data->More Filters->Advanced Filter then selected the whole column and checked Options->No Duplications.

Looking at the docs online this should leave me with a unique text entries, but I get an error This range does not contain a valid query.

Does it mean that I am doing something wrong or this filter cannot be applied to text entries?

Solution 1

I've come across the same situation. After lot of trials, I've found that the error is caused by the range selected is not the same as the range entered in the selection box: Like @simlev had said, if you had choose cell A1:A3, in the selection box, you need also enter A1:A3, but if you had choose A2:A3, but in the selection box, you had enter A1:A3, that will cause the problem we had seen.

Solution 2

This is working for me, only difference I notice is I can't select the entire column but just a limited cell range. It also works copying the filtered data to a different location.

1. Select desired cell range or entire column
2. Menu Data->More Filters->Advanced Filter
3. Select desired cell range
4. Expand Options in the Advanced Filter dialog
5. Check the No duplications checkbox
6. Click the Ok button

Comments

[P A N]

The key to getting this to work is to select the "Read Filter Criteria From" which needs to include all the values that MAY be allowed to pass the filter. Once all values are selected, check No Duplications to make sure that only one record out of that Filter Criteria is kept. Assuming that the Filter Criteria is a single column, that will filter out duplicate rows after the first unique value in the Column. The unique row will keep its cells over multiple columns if they exist, so you need only select a single column for the Filter Criteria (if it indeed contains all the Filter Values).

[P A N]

I might add that Calc's system here isn't very intuitive. It seems that the amount of cells in the Filter Criteria must be equal to the amount of cells in the original selection, or else it will complain about an incorrect range. I.e. the same range must be selected twice; before and after opening the Advanced Filtering menu. Furthermore, unfortunately I've been unable to figure out how to "Copy" the resulting unique rows to a new sheet, but I'm sure it's possible with some further fidgeting...

[simlev]

@Winterflags To copy filtered results to a different sheet just set Copy results to: to e.g. $Sheet2.$A$1.