1

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?

Improve this question
1
  • Can you provide a follow-up on this topic: did you solve the problem, and how? Was any of the answers below useful to you? Please consider accepting/upvoting them. Did you proced otherwise? Please post your own answer and accept it instead. – simlev Mar 14 '17 at 8:59
1

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

Filter cells in place

Copy filtered cells

Improve this answer
3
  • 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 Aug 9 '17 at 12:09
  • 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... – P A N Aug 9 '17 at 12:23
  • @Winterflags To copy filtered results to a different sheet just set Copy results to: to e.g. $Sheet2.$A$1. – simlev Aug 9 '17 at 12:34
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.

Improve this answer
0

You get that error because you are not selecting the cell range. When in advanced filter, you have to write something like $Sheet1.$A$1:$A$256 in the top text box, or you can click the top-right button (shrink) and manually select the cells.

Note that you can also use standard filter, checking No duplications and unchecking Range contains column label if you want to use also the first line.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.