For larger spreadsheets, especially with considerable repetition of values, the auto-completion feature in new cells is extremely helpful. However, the algorithm for suggestions could be much smarter than it is.
Consider the following very simple example spreadsheet. The first 4 cells have been filled in with values "xa", "xb", "xb", "xc", and the user is part-way through entering the data in cell A5, having typed "x".
ROW 1 xa
ROW 2 xb
ROW 3 xb
ROW 4 xc
ROW 5 x...
Currently, the autocomplete proposes "xa". This is doing an alphabetical search back through the column A.
I think that this is the worst possible algorithm to choose.
A smarter choice would be "xc", i.e. a reverse-search though history starting
at the current position.
A possible alternative (maybe even better) would be "xb", i.e. a search for the most common match in the column.
The reason why this matters to me is that I have a spreadsheet with about 1000 rows, where the columns are like the following:
1: Long string of text
2: Long string of text
3: Long string of text
4: Long string of test test
5: Long string of text
6: Long string of text
7: Long string of text
[repeat for 1000 rows]
1001: Long string of text
1002: Long string of text
in this case, every single autocomplete is wrong, and I have to type out the entry nearly in full, just because of one line which (although it is correct), I entered 3 years ago, and it has been messing up autocomplete every day since then!
Thanks for your time, and your work on a great program.
Thanks for writing the idea. I'm not sure how that will work out in various (different) situations, but...
(In reply to comment #0)
> 4: Long string of test test
> 5: Long string of text
> 1003: L....
> in this case, every single autocomplete is wrong, and I have to type out the
> entry nearly in full, just because of one line which (although it is
> correct), I entered 3 years ago, and it has been messing up autocomplete
> every day since then!
always Ctrl+Tab cycles through the possible autocomplete options...
I think that helps in your case?
Ctrl-Tab does help a bit, but it's much more awkward than a smarter auto-complete. It needs 2 extra keystrokes (which aren't easy to type fast), and then we may have to Ctrl-tab multiple times. Furthermore, it's a non-discoverable and less-intuitive way to work.
There is one extra refinement needed - in that we aren't always appending data at the bottom of a column; sometimes we insert in the middle or at the top.
(1) Current algorithm is:
Scan from top of column for match, accept the first one.
(2) Improved algorithm.
Scan outward (up + down) from the current cell for a match, accept the
(3) Optimised algorithm:
Scan the entire column and count matches. Accept the most popular match.
Given that (3) may be harder to write, and possibly worse at runtime, I think that (2) would suffice - it should be easy to write, and would catch most of the common cases.
Let's set this to NEW.
option 2 would be a nice enhancement!
I found an older report that is requesting a similar change, I will add a comment there to check this out.
*** This bug has been marked as a duplicate of bug 43742 ***