Bug Hunting Session
Bug 78545 - EDITING: spreadsheet cell autocomplete improvements (better algorithim)
Summary: EDITING: spreadsheet cell autocomplete improvements (better algorithim)
Status: RESOLVED DUPLICATE of bug 43742
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.3.3 release
Hardware: x86-64 (AMD64) Linux (All)
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: AutoCorrect-Complete
  Show dependency treegraph
 
Reported: 2014-05-10 21:21 UTC by Richard Neill
Modified: 2018-06-07 14:33 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Richard Neill 2014-05-10 21:21:51 UTC
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".
   
          COL
          A

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
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!

--
Thanks for your time, and your work on a great program.
Comment 1 Cor Nouws 2014-05-11 08:33:32 UTC
Hi Richard,

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?
Best,
Cor
Comment 2 Richard Neill 2014-05-11 15:53:44 UTC
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
  nearest one.

(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.
Comment 3 Buovjaga 2014-11-24 11:31:25 UTC
Let's set this to NEW.
Comment 4 Luuk 2014-11-24 12:14:56 UTC
option 2 would be a nice enhancement!
Comment 5 Buovjaga 2018-06-07 14:33:00 UTC
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 ***