Excel Auto Complete inconsistency.

VoucherMan

I have a spreadsheet which contains a list of names.

There are currently about 80 different names in the list which has about 800 entries so far.

Sometimes when I start to enter one of the names the autocomplete will work, other times I'll type in the complete name and nothing happens.

Anyone encountered this and found a solution or an alternative way to populate a cell? I've tried a validation list but this doesn't bring names up any better. Just stops me entering one that's not on the list.

real1314

2 different people with the same first name?
Or possible 2 different entries for the same person - one with 1 space, the other with 2?

You can get rid of the space thing by doing a replace of " " with " " (ie. replace 2 spaces with 1.

Or names with a space at the end?

You can get rid of extra spaces at the end using trim - set up a column with "=TRIM(A1)" copy it down for all cells, then copy and paste special-values from there to your original column

VoucherMan

Definitely no spaces at the end. Since adding a validation list it will not accept the name with extra spaces.

As for the names some such as lewis and caroline are the only names starting with the respective letters.

The name lewis had to be entered fully, it's last occurrence was 302 places up the list. caroline was completed with the autocomplete but not until I'd typed the 4th letter. It's previous entry was over 600 up the list.

It only seems to occur with names not recently entered, but even this is not consistent. A Google search did bring up similar examples, although some of these were caused by gaps in the list. Mine has none so I've ruled that out. Didn't find a solution there though.

paddyrg

Autocompletion is a helper function, not to be relied on for data validation - you can use various lookups for guaranteed results if that helps?

Stompa

It doesn't sound like your problem, but I know that there's a limit on the number or rows Excel checks back from the row on which you're currently entering data. That limit varies according to which version of Excel you're using, so it's higher in later versions.

System

Excel 2003 is around 215 rows but I can't find any figure for 2007/2010

VoucherMan

Stompa wrote: »

It doesn't sound like your problem, but I know that there's a limit on the number or rows Excel checks back from the row on which you're currently entering data. That limit varies according to which version of Excel you're using, so it's higher in later versions.

!!!!!! wrote: »

Excel 2003 is around 215 rows but I can't find any figure for 2007/2010

I'm using 2007. I did at first think this was the problem but the fact that it completed a name over 600 lines up the sheet threw me.

paddyrg wrote: »

Autocompletion is a helper function, not to be relied on for data validation - you can use various lookups for guaranteed results if that helps?

I've had a look through all the lookup related functions and nothing stands out. I'm open to suggestions though.



End of the day it's only to save me typing a few letters (such as graham instead of gra) maybe three or four times a week.

For now I've managed to limit the poor results of autocomplete. I tend to sort all the data by column A (name) then column C. Unnecessary but I find an alphabetical list neater (spreadsheet OCD?), then apply a filter or two to make searching easier. It also meant that names like andrew were further from the end than steven, and getting further as the list got bigger.

Instead I'll try sorting the data by column C first. When I add the filters the results will be in the same order. The names will be much more randomly placed throughout the list so there's more chance of a match.
I've also added a complete list of the names further down the sheet (starting about 50 lines down) that should help bring all the names 'within range' of the autocomplete.




Had a bit of an experiment to try and find a figure for !!!!!! but the results were too inconsistent. The name that originally autocompleted didn't work when I tried again. Similarly for a name which was almost 700 lines further up the list.

Closer to the end of the list a completely unique name 200 from the end had to be typed in fully. One a bit further up completed after the first unique letter was entered.

StumpyPumpy

You'll never get a definitive number of rows for Autocomplete. All you will be able to say is that it will work for 50 rows above and 50 rows below the active cell at any time. Whenever Autocomplete refreshes it scans those rows then adds to its list during idle cycles.

So, if the value you are looking for does not appear at first, you will find that if you come out of cell edit mode (idle does not happen during edit) and wait a (indeterminate) time you can then edit the cell again and it will appear in the Autocorrect. Well, maybe, there are other things that affect it - background XL processes can cause the Autocorrect to be recalced, resetting the row count and available string and pointer space also have an impact. Obviously, I'm not suggesting you do this in real life, but at least it should explain the apparent inconsistencies and save you time trying to figure them out.

One workaround I can think of is to use the Autocorrect list (Alt+Down Arrow) to pick your name if it doesn't immediately generate for you. For instance, using your example, you type in Gra,it doesn't autocomplete, so you press Alt+Down Arrow and a list appears with Graham highlighted ready to select. Again, this may not be helpful in your circumstances as you don't have that many letters to add, but it may be useful for other columns.

The Autocorrect list is also useful if you have cell values like Graham1 and Graham2 because you can type Gra, then pick out the correct Graham without having to type the whole value, so it is well worth getting used to using it. Unfortunately, it also relies on idle cycles to do part of its work so in large lists it can still be inconsistent and so is not a 100% solution.

SP

paddyrg

http://spreadsheets.about.com/od/advancedexcel/ss/2012-10-17-excel-drop-down-list-pt-3-different-workbook_5.htm

Is something like this any help, using the inbuilt data validation tools, which allow you to create dropdown lists of approved values

VoucherMan

paddyrg wrote: »

http://spreadsheets.about.com/od/advancedexcel/ss/2012-10-17-excel-drop-down-list-pt-3-different-workbook_5.htm

Is something like this any help, using the inbuilt data validation tools, which allow you to create dropdown lists of approved values

I did try that at the start but it's easier to type in the extra few letters than mess around with a drop menu. Unless I start getting names like rumplestiltskin