Here is a technique that I've used countless times when crafting an Excel database project for clients. The goal is to show all records that partially match a lookup string.
In the sample file, there are 10,000 records and as you can see the partial match lookup is very quick. It supports wildcard characters.
While this is entirely an Excel application, what we are doing here is equivalent to a traditional database SQL statement of:
SELECT *
FROM Data
WHERE (((Data.Description) Like "*search_string*"));
So how do we do this in Excel. It could be done using just formulas, but this is a VBA project. It leverages the brilliant FindAll() function by
Chip Pearson. All of the code is in the Sheet2 code module. Have a look. It's succinct, potent.
The file works in all versions of Excel from 2000 forward.
So tell me how you do partial match lookups?
Here is the file.
--- UPDATE ---
As we have seen many times before, Excel always has multiple ways of doing things. Sam sent me an awesome tip. I revised the workbook to use the tip and it is now much faster and the code is better too. Chip's FindAll() function is still brilliant, but it was not needed here, so I've removed it in the new version. It is useful for many, many things and I'll write about it often. Sam's tip was to use the AdvancedFilter method of the Range object. The previous version was more than adequate as long as the search string was three or more characters long. Now it's instant even if one character long. In fact, it's instant even if zero characters long!
Here it is.
As promised in the comments, here is the ADO way of doing the Partial Match Database Lookup. I have taken Nikolai's example and reworked the code somewhat to conform to my style of programming. Using ADO is very, very powerful, as it unlocks the entire universe of real databases, such as SQL Server, Oracle, MySQL, Access, and countless others. Virtually all Windows computers have ADO installed and ADO can read Excel files even if the machine does not have Excel installed! Of course for our purpose, we will be using it from Excel so that is beside the point. But you see, ADO, is a separate library that dozens of programming languages can use. So to use it from Excel we need to reference the ActiveX Data Objects Library from the Tools-References menu of the VBA Editor.
Over the coming months I will share many applications that use ADO to interact with databases. But there is one hiccup that you need to know about. If you are using it to interact with Excel data in the same workbook where you are using ADO, there is a problem. Each time you call the ADO library, a bit of memory will be used up on your computer and you will never get it back until you reboot your machine. This is called a memory leak, and it is poor programming practice. This particular memory leak is a recognized bug and Microsoft has published a KnowledgeBase article about it on their support website:
So you should never do this. However, for instructional purposes I have setup Nikolai's workbook with a check box that allows you to toggle between using local data, and having the VBA code create a copy of the data in a new workbook and accessing that instead (which is the correct way to do it). To see the memory leak in action, just open the Windows Task Manager to the Processes tab while you have the workbook open. Sort the list by Image Name. Find the Excel.EXE process and select it with the mouse. Now start using the Partial Match Database Lookup with ADO workbook to do searches. You will notice if you have the External Data Method check box checked that the memory will temporarily increase for the Excel.EXE process as ADO is invoked; but you will also notice that the memory is quickly given back to the Windows operating system. This is good and the way things are supposed to work.
Now un-check the External Data Method check box in the workbook and do some searches. You will notice that each time a search is done the memory spikes, but that memory is NEVER released back to Windows. This is the memory leak. If you keep using ADO in this fashion eventually you will run out of memory and your computer will crash. The only way to get that memory back is a reboot.
So this file is interesting and instructive. It shows you how to use a very powerful library to access data, and it demonstrates the correct and incorrect way of doing so, allowing you to toggle back and forth between the two.
Thank you Nikolai for the file. He runs a fantastic website on chess, so please check it out:
Here is his modified Parial Match Database Lookup file that uses ADO:
