Autocomplete after 100 rows in Calc

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 :slight_smile:

Thank you!

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.

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

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

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.

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

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… :confused:

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.

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

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.

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.

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

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

1 Like

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.

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?

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.

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.

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? :slight_smile: quite strange, really.

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…