Combobox with search suggestions

Question

I created a combobox of which the list of options updates whenever you type in the box. Just a bit like entering a search in google works with suggestions. I followed this tutorial to do so: https://trumpexcel.com/excel-drop-down-list-with-search-suggestions/

There are 4 columns of data in the sheet that make this work, combined with a named range, and a short piece of VBA to update the combobox. The combobox is in linked to cell B3. And data is in E:H starting at row 3.

• First column includes the options for the combobox. There are 5619 possible options to choose from.
• Second column has the following formula to determine if the entry of the combobox is in the text of the same row in the first column: =--ISNUMBER(IFERROR(SEARCH($B$3,E3,1),""))
• Third column has the following formula to establish the amount of matches found: =IF(F3=1,COUNTIF($F$3:F3,1),"")
• Fourth column has the following formula to return the list of possible options based on the value typed in the combobox: =IFERROR(INDEX($E$3:$E$22,MATCH(ROWS($G$3:G3),$G$3:$G$22,0)),"")

The code to update the combobox is as follows:

Private Sub ComboBox1_Change()
ComboBox1.ListFillRange = "DropDownList"
Me.ComboBox1.DropDown
End Sub

With DropDownList Being a named range that selects all the names in the fourth column.

The problem I face here is purely performance. It is really slow and unresponsive on a PC. Let alone deployed on a mobile device, which I intend to.

The problem is ofcourse within the fact that there are 5600+ options for the combobox, and therefore 4*5600 formula's to be recalculated at every character entered in the combobox. I was wondering if there is a way to do with less helper columns, and maybe more VBA to reduce the calculation times required?

Answer 1

To combine the second and third column into only 1 helper column I deviced the following formula's:

In the first cell of the second column I put the following formula: =IF(ISNUMBER(SEARCH($B$3,E3,1))=FALSE,"",--ISNUMBER(SEARCH($B$3,E3,1))

In the second cell and down I wrote the following formula: =IF(--ISNUMBER(IFERROR(SEARCH($B$3,E4,1),""))=0,"",COUNT($E$3:$E3)+1)

Answer 2

The final column's call to MATCH() is triggering some slowdown. The 0 parameter forces a linear search, so the whole thing takes time proportional to n^2 where n is the number of elements. This needs to be avoided. The list of count in column G (or wherever you put it, if you got them combined) is in monotonically increasing order, so it is permissible to use 1 or true as the third parameter to SEARCH(), causing it to perform a binary search -- but unfortunately it might pick any element with that value. In order to end up at the right spot, take advantage of the other feature of binary search: it picks the latest value less than or equal to the searched item. Tweak the value slightly and end up at the right spot!

=IF(ROWS($G$3:G3)<=MAX($G$3:$G$1000),
 INDEX($E$3:$E$22,1+IFERROR(MATCH(ROWS($G$3:G3)-0.5,$G$3:$G$22,1),0)),"")

This depends on a helper cell somewhere else, named MaxOfColumnG, which is just the =MAX($G$3:$G$1000) or whatever.

How it works: The list in column G ("third column") is something like

0
0
0
1
1
1
2
2
3
4

The formula in H3 wants to find the first 1. So we search for 0.5. We find the largest value less than or equal to 0.5, which ends up being the last 0. Add on one more and we're at the right place. I had to change the IFERROR to an IF but we already know the max-index. In addition, the IF() will short-circuit and not perform the MATCH() unless it is necessary.