Autocomplete after 100 rows in Calc

Giorgi · February 22, 2019, 8:26am

I had a problem I was struggling with for the few days, and finally I’ve identified and narrowed down this problem to the particular question.
I have some tabular data (let’s say, dictionary, or some listing or whatever…) in LibreOffice Calc. As this document has hundreds of records, I can’t check for duplicates for particular column entries manually, when I’m entering the new entry (row). I.e. if data has 5 columns, and I want the first column to be unique, when I’m entering the word there, MS WORD automatically highlights autocomplete if that word was already entered somewhere above. LibreOffice does that as well, BUT ONLY in the case if current column is NO MORE THAN 100 rows is far from the possible duplicate value. E.g. if 4th row’s first column value is “String”, and if I start typing “str…” in any row <=104, autocomplete highlight would work, if I’ll try that in row 105, autocomplete won’t work, thus I can’t be able to know whether the data I’m entering is already filled in somewhere above.

Sorry for a long question, but I wanted it to be as clear as possible. I really need your help, as I’m the one who tries to move from MS to LibreOffice. Prove yourself, please

Thank you!

Hrbrgr · February 22, 2019, 9:18am

It could be a bug. At Bugzilla you can check if there is already a bug for this problem.

And please read the guidline. Which OS and LO-Version do you use? Which file types do you use? Please send more details.

Giorgi · February 22, 2019, 9:34am

I use Windows, LibreOffice 6.2.0 Calc. Problem is in .ods file.

Hrbrgr · February 22, 2019, 10:16am

Can you anonymize the file and upload it here? To upload please edit your question.

Giorgi · February 22, 2019, 10:26am

No, I can’t. It contains some data… but you don’t need that. You can just reproduce what I’ve said: For instance, create dictionary, with two columns [Eng] and [Spanish]. At some point you’ll have more than 100 rows… so if on the 101st row you are entering a new word, Calc won’t highlight with autocompletion whether that word has already been used somewhere above or not… but it you enter the same word before the distance of 100 rows, than it will.

mikekaganski · February 22, 2019, 11:26am

I can confirm that a limit does exist; however, for me (using 6.2.1.1 on Win10), it’s 202 rows.

Giorgi · February 22, 2019, 11:42am

But anyhow… even 5000 is not good to have as limit. I don’t want to have this limit… I want Calc to highlight me the possible duplication even after 5000 rows… what’s that? why’s that? it’s so saddening and disappointing…

mikekaganski · February 22, 2019, 11:48am

one’s feelings are irrelevant on Ask site. What’s relevant is if you filed an enhancement request, so that developers evaluate and implement what you need.

Giorgi · February 22, 2019, 6:20pm

@mikekaganski, are all Russians that impolite and rude like you are? I hope not.

mikekaganski · February 22, 2019, 6:29pm

If you cannot see two good advises here handed to you - one being to avoid expressing one’s feelings in public resources, because that may hurt others, and cause unnecessary problems; and the other being to file a request to let developers know what you need - and take that as offence, then I cannot help.

Giorgi · February 22, 2019, 6:30pm

Google for some online courses on how to communicate and behave in public, how not to respond in comments and etc. I can’t help more as well. Ciao.

Hrbrgr · February 23, 2019, 7:50am

Emotions are sometimes the luxury of people who want to avoid reality.

eruciform · January 16, 2023, 6:37am

no one else bothered to make a bug fix ticket for this so i did

https://bugs.documentfoundation.org/show_bug.cgi?id=153038

erAck · February 22, 2019, 4:08pm

There is some limit (whether it’s 100 or 200 or 500 I don’t recall), for a reason, because in a populated column you don’t want to wait for a list box of a million entries for each cell you enter.

mikekaganski · February 22, 2019, 4:12pm

a list box? the suggestions are presented as pre-filled and pre-selected text to the right (LTR) of cursor iiuc. I suppose that simply finding a first value matching the entry (no matter how far) is enough?

Giorgi · February 22, 2019, 6:22pm

What List box? I think you didn’t get the problem erAck. Why shall I have millions of entries in one column even if there really are similar records in say millions rows? the aim here is to just autofill the box IFF there is at least one row which has the same value in the same column.

erAck · February 22, 2019, 7:09pm

I confused that with Alt+Down which lists all previous entries. Might even be those two use the same code. However, to suggest/autocomplete a pending input still all rows of the column would have to be searched with each key input until there is no match.

Giorgi · February 23, 2019, 9:14am

Lol. Now I’m trying to register a new account and submit a bug-report. It says my email will go public to all the registered members… seriously? what if strange thing happens and I don’t love the idea to spread my email to everyone around? quite strange, really.

Wanderer · January 16, 2023, 8:02am

I’d suggest a simple work-around and implement this check yourself.
A COUNTIF for A$2:A10 when you are in A11 can check for the number. I would put this in a conditional formatting and use a “warning” color for the background.
.
But as I learned in another thread not to solve problems by work-arounds you may prefer the generic answer

"Libreoffice Calc does not currently support this functionality.
cited from Data Validation, cell range needs full text search - #20 by gh0stn0te
and you have to request the enhancement yourself at bugzilla.
.

Welcome on the path, but if you expect a clone of MS-Office you will be disappointed. So choose wisely and for advanced features test, if things work as you expect.
.
PS: As LibreOffice comes with “database included” you could simply enter your data in a database. UNIQUE-constraints are enforced by the database then…