Excel text-based auto-complete works for some items, doesn't for others

[Balintn]

Excel 365 on Windows 10

I have an Excel sheet with about 2000 rows. In one column, there are names, repeating from a few hundred individual values.
Expected: when I type a name in a new row, in this column, auto-complete should find the matching name and fill it in.
Observed: for some names it works, for others it doesn't.

I also have a solution.
Step 1:
The content of the name column changed a lot. Historically, it contained pairs of names, sometime starting with existing single ones (example: "John Doe", "John Doe & Jane Doe").
I got fed up with having to type these in their entirety, moved them into two separate columns (Name1 and Name2), so the multiple names starting the same way are now removed.
Expected: with the new, restricted set of values in column Name 1, auto-complete should start behaving as expected: "John Doe" being the only item starting with "John", typing "John" should be auto-completed to "John Doe".
Observed: it doesn't work. Typing "John" offers no auto-complete.

Attempt 1 (not working)
Created a new column ("Name 1 Copy"), copied content using (CTRL-C / CTRL-V). In the new column ("Name 1 Copy") auto-complete still doesn't work.

Attempt 2 (working)
Created a new column ("Name 2 Other Copy") copied content using (CTRL-C / Paste special / Values). In the new column, auto-complete works as expected.

Something I don't understand
I tried to reproduce it with just two lines, "John Doe", "John Doe and Jane Doe", saved the file, reopened, removed "John Doe and Jane Doe", to see whether the erroneous behaviour shows up. It didn't. It may have something to do with the long history of the file, or with other column content around it. But the solution in my large file work, so won't dig deeper.

 

[jasonb75]

Auto complete in a cell only works when you have typed enough to make the entry unique (from your post it appears that this should be the case).
It needs to be a continuous range of cells (minimum of 3 cells if I remember correctly). An empty cell will break the cycle.

Are the earlier entries clean? No stray leading spaces or zero width characters if the data was copied / imported from elsewhere? Anything like that could cause a mismatch.

Other than the above, I can think of no obvious reason for it to fail.

 

[Balintn]

Excel 365 on Windows 10

I have an Excel sheet with about 2000 rows. In one column, there are names, repeating from a few hundred individual values.
Expected: when I type a name in a new row, in this column, auto-complete should find the matching name and fill it in.
Observed: for some names it works, for others it doesn't.

I also have a solution.
Step 1:
The content of the name column changed a lot. Historically, it contained pairs of names, sometime starting with existing single ones (example: "John Doe", "John Doe & Jane Doe").
I got fed up with having to type these in their entirety, moved them into two separate columns (Name1 and Name2), so the multiple names starting the same way are now removed.
Expected: with the new, restricted set of values in column Name 1, auto-complete should start behaving as expected: "John Doe" being the only item starting with "John", typing "John" should be auto-completed to "John Doe".
Observed: it doesn't work. Typing "John" offers no auto-complete.

Attempt 1 (not working)
Created a new column ("Name 1 Copy"), copied content using (CTRL-C / CTRL-V). In the new column ("Name 1 Copy") auto-complete still doesn't work.

Attempt 2 (working)
Created a new column ("Name 2 Other Copy") copied content using (CTRL-C / Paste special / Values). In the new column, auto-complete works as expected.

Something I don't understand
I tried to reproduce it with just two lines, "John Doe", "John Doe and Jane Doe", saved the file, reopened, removed "John Doe and Jane Doe", to see whether the erroneous behaviour shows up. It didn't. It may have something to do with the long history of the file, or with other column content around it. But the solution in my large file work, so won't dig deeper.

I just found that it didn't work after all.
But found what caused the issue: in some cells I had trailing spaces ("John Doe" and "John Doe "). When setting a filter for the column, they show as a single entry ("John Doe"), but auto-complete considers them different. Removing trailing spaces solved the issue.

 

[Balintn]

Auto complete in a cell only works when you have typed enough to make the entry unique (from your post it appears that this should be the case).
It needs to be a continuous range of cells (minimum of 3 cells if I remember correctly). An empty cell will break the cycle.

Are the earlier entries clean? No stray leading spaces or zero width characters if the data was copied / imported from elsewhere? Anything like that could cause a mismatch.

Other than the above, I can think of no obvious reason for it to fail.

Spot on, thanks!