Autocomplete using values from a long way up the column

[StuartR]

I have a worksheet with about 9,000 rows. One column in this worksheet has text values, and when I start typing a new entry it often offers to complete this using data from an earlier cell. I guess this is what Excel calls autocomplete, but I may be wrong.

This feature is very useful, but it only works with cells that are fairly close above where I'm typing, I haven't done any testing to see exactly how far up the entry has to be before Excel ignores it.

Is there any way to get Excel to look further up the workbook to find matching entries? This would save me a lot of typing.

[HansV]

As far as I know, there is no way to tweak Excel's autocomplete algorithm. The further away a matching entry is, the more characters you have to type before Excel tries to autocomplete.

But you can do the following: type one or more characters, then press Alt+down-arrow.
Excel will display a drop-down list of all entries above it, sorted in alphabetic order, with the nearest match selected. Use the arrow keys to select another one if necessary, then press Enter.

[ChrisGreaves]

But you can do the following: type one or more characters, then press Alt+down-arrow.

[StuartR]

This sounded perfect, but it doesn't match entries that are high up the column, it just displays the alphabetical list with the first entry selected.

Edited to add. After I typed the first 13 characters it did find a match for an entry 2972 rows above, but it didn't find it when I just typed 12 characters

[DocAElstein]

...only works with cells that are fairly close above where I'm typing, I haven't done any testing to see exactly how far up the entry has to be before Excel ignores it.....Is there any way to get Excel to look further up the workbook to find matching entries?...

Hello Stuart.
I think it’s not limited by distance, (although I have not tested for very large ranges, there might be some final limit, I don’t know) … - it seems to work as long as there is a unbroken “chain” of touching cells with text in them. It seems it does not necessarily need to be cells in the same column**

If you can use some adjoining cells, ( or columns, etc, ( hidden if you like) ) , then you can get the effect you want: **It seems that as far as Excel auto complete is concerned, adjoining, or rather “touching” cells have the same effect as if a cell itself has something in it. In other words , in simple terms, fill neighbouring cells to the ones you wont AutoComplete to work on with some arbitrary text, even a single character will do..

( Another thing you can so is put in a “formula text .Value”, like
=""
in all your empty cells, and that will have a similar effect to make AutoComplet work, then just overwrite the cells you want to put stuff in )

I might not have explained that too well, nevermind........Some examples of what I am talking about in the attached file. In that file, cells highlighted in similar colours are all “linked” such that AutoComplete works in the highlighted column range , even though all the cells in the column range are empty, or appear so. I don’t think there is any limit (within reason) on how big you can extend those column ranges to, but I have not done extensive tests yet.

AutoComplete.JPG

AutoCompleteValueFromAlongWayUpColumn.JPG

( The effect I demo from column J can be used to select what words are included in the AutoComplete, or put another way, you can isolate, ( bypass as it were), some entries in a column so they are not included in the AutoComplete suggestions. (But note you can only do such "tricks" to a certain extent, - one thing that can chuck a spanner in the works is if you later add something in a cell that then "connects" something you did not want connected) )


Alan

[DocAElstein]

...The further away a matching entry is, the more characters you have to type before Excel tries to autocomplete....

Interesting. I never noticed that yet. But I have not done detailed tests on this AutoComplete thing yet.

I just saw a similar effect, which might sometimes be mistaken for the effect you mentioned...
If have entries like
abc1
abd2
, then AutoComplete wont give me any suggestions if I type
ab
, but it will give me the appropriate suggestion if I type
abd
or
abc

I suppose this is because AutoComplete can only give you one suggestion, and if it has two, it is written such as to give you nothing rather than one of them – it “waits” until it can give you a single match ?


Edit later..... Actually Hans that might have been the effect you were referring to? .... if further away a matching entry ... means a longer word to get a unique match, or some such...

[HansV]

After I typed the first 13 characters it did find a match for an entry 2972 rows above, but it didn't find it when I just typed 12 characters

I'm afraid that's how the algorithm works - the further away an entry is, the more characters you have to type before AutoComplete kicks in.

[StuartR]

I think it’s not limited by distance, (although I have not tested for very large ranges, there might be some final limit, I don’t know) … - it seems to work as long as there is a unbroken “chain” of touching cells with text in them. It seems it does not necessarily need to be cells in the same column**

I have just checked. This worksheet has data in every cell in columns B, C, D, E, F, G, H, and I between row 9 (which has my headers) and row 8930 (where I am entering new data)

[DocAElstein]

Interesting. I just assumed it would have been a fairly big limit, maybe somehing like the row count of up to Excel 2003 / xls worksheet size type stuff, as many such things seem to have that typical large limit.
Maybe I will experiment one day and see if I can see what the actual limit is.

[DocAElstein]

... One column in this worksheet has text values, and when I start typing a new entry it often offers to complete this ..
This feature is very useful, ....
Is there any way to get Excel to ..... find matching entries? ......

Hi Stuart, ( and anyone else )
I did a solution idea suggestion here just now:
https://eileenslounge.com/viewtopic.php ... 06#p297006

I done you a slightly modified version of it while I was at it in this file: PseudoIntellisenseAutoCompleteSearchStuartRtsWordWotHeestryintfind.xls
https://app.box.com/s/a8o6qtdww537n18vo7d0vavx4rll8qry




Its not what you wanted, but it might be of passing interest , just as another way to do something similar to what you wanted.
It does involve an extra UserForm thing, but it's a very simple UserForm, and the main coding behind that UserForm is in a Private Sub TextBox1_Change() thing, and its not so much coding:

Code: Select all

 Private Sub TextBox1_Change()
Me.ListBox1.Clear

'Find (Next)  https://stackoverflow.com/questions/49094391/excel-vba-range-findnext-v-range-find-what-have-i-missed/49181464#49181464
Dim rngFnd As Range, Lr As Long
 Let Lr = Range("A" & Rows.Count & "").End(xlUp).Row + 1 '  +1 is required to make the search work, that is to say terminate if our last found cell was the last one
 Set rngFnd = Range("A1:A" & Lr & "").Find(What:=Me.TextBox1.Text & "*", after:=Range("A" & Lr & ""), LookIn:=xlValues, LookAt:=xlPart)
    If rngFnd Is Nothing Then Exit Sub
    Do While Not rngFnd Is Nothing
     '                Debug.Print rngFnd.Value ' or  Do anything you wanna do                                                                                         http://www.youtuberepeater.com/watch?v=8GoN-y9irn4&name=Eddie+and+the+Hot+Rods+Do+anything+you+wanna
     Me.ListBox1.AddItem rngFnd.Row                                             'Row number
     Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = rngFnd.Value              'Name
     Set rngFnd = Range("A" & rngFnd.Row + 1 & ":A" & Lr & "").Find(What:=Me.TextBox1.Text & "*", after:=Range("A" & Lr & ""), LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)   '  https://msdn.microsoft.com/en-us/VBA/Excel-VBA/articles/range-find-method-excel
    Loop
End Sub

The rest of the coding in the file is just some extra stuff to make a demo for you of its use… like this…..

Demo:
In the file, PseudoIntellisenseAutoCompleteSearchStuartRtsWordWotHeestryintfind.xls , is a long list , about 17000 rows.

Now lets go back to your original requirement:
Let’s say you want to type something in , way down at row 17800, and let's say wot you actually want to write in is already in the list, ( in this example file at row 500 ) and it is actually
StuartRs Words Wot Hees tryin t find
But let's say for demo purposes that you either can’t quite remember exactly all that, or you can, but you don’t want to type it all out again.
So….

Go down and select cell 17800, ( or any cell you want to write it in)
The UserForm should then pop up
In the top text box start typing any bit of that set of words. (For example, start to type Stuart .... )
As you type
S
or
st
, you will get hundreds of suggestions in the list box, - as you might expect, since s or st is in a lot of words.
But as you get as far as
stu ___1 ,
, you will get a shorter list of suggestions, and approx the fifth one down is the one you want.
( if you type stua , then you will just get the single suggestion of wot you want )
Select that fifth suggestion from the ListBox list___2
, and then close the UserForm ___3

Bingo! – the set of words you want are pasted in the cell for you

Stuatrst word hees tryin t find.JPG

Alan



_.______________________________________________________________-
PseudoIntellisenseAutoCompleteSearchStuartRtsWordWotHeestryintfind.xls
https://app.box.com/s/a8o6qtdww537n18vo7d0vavx4rll8qry


_.______________________________________________

( Here is the full story to the actual UserForm that I developed, from a few years ago, starting from about here,
https://www.excelforum.com/excel-progra ... ost4673161
, But I wouldn’t recommend trying to wade through it. I was on some serious mental trip back then I think, and with a slightly more sane mind just now I can’t figure out what I was rambling on about…… )

[StuartR]

That is a clever approach. I'm not sure if I want to add Macros and forms to this workbook, but I will think on it for a bit before I decide.