Partial Text String Lookup from a List

Thanks, I've attached a sample workbook to my original post.

What I want to achieve is when "B1" is typed into the search criteria, it should return PERSON B.

However, because PERSON A has the partial string B1 (BB1), it thinks that this is the result because this is the first string it finds with "B1" in.

Hi lookingforhelp1,

I think part of your problem is that there are multiple codes in one cell, and the formula finds the first EXACT MATCH using the WildCard Search and both 'B1' and 'BB1' match the search for '*B1*'.

Doing what you want is beyond my Formula capability, but if VBA is OK, try the attached file which contains the following code. The file uses a UserForm which allows you to search for a partial or exact match (case insensitive). All matching entries are returned in a ListBox. If you 'Double Click' an item in the 'ListBox', the cursor will move to the cell for the 'Person' associated with the Code.

In UserForm1 code module:

Please Login or Register  to view this content.

In ordinary code module ModUserForm1:

Please Login or Register  to view this content.

It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Please Login or Register  to view this content.

This option requires all variables to be declared and will give a compiler error for undeclared variables.

To install the software in another file:
a. Make a backup copy of your file.
b. Open my file.
c. Presss ALT F11 to access VBA.
d. Press CTRL R to accces Project Explorer (if is not already visible).
e. Right Click the following modules and Export them (will not affect this file):
(1) ModUserForm1
(2) UserForm1
f. Close my file.
g. Open your file.
h. Press ALT F11 to access VBA.
i. Press CTRL R to accces Project Explorer (if is not already visible).
j. Right Click any module from your file in Project Explorer and import the following modules:
(1) ModUserForm1
(2) UserForm1
k. Create a 'Forms' CommandButton or Create a Shape in your file.
Developer > Insert > Select 'Forms' CommandButton and Place on Sheet. Assign Macro 'DisplayUserForm1'.
or
Insert > Shapes > Select Shape and place on the Sheet. RightClick the Shape and Assign Macro 'DisplayUserForm1'.
l. Software Installation completed. Save your file.


To enable Macros and to Run Macros see the following:
http://office.microsoft.com/en-us/ex...010031071.aspx
http://office.microsoft.com/en-us/ex...010014113.aspx
If help is still needed do a google search for 'youtube excel enable macro' and/or 'youtube excel run macro'.


To access Visual Basic (VBA) see:
http://www.ablebits.com/office-addin...a-macro-excel/
a. Click on any cell in the Excel Spreadsheet (may not be needed).
b. ALT-F11 to get to VBA.
c. CTRL-R to get project explorer (if it isn't already showing).
d. Double Click on a 'Module Name' in 'Project Explorer' to see code for that module.

Lewis

Thanks for your help, I tend to stay away from VBA due to only having limited knowledge.

I managed to get the goal working by adding a space after the first wildcard though (who'd have thought it!).

It's an excellent practice to stay within your comfort level.

Excellent job using the space.

Here's an idea that may help. Instead of separating the items with commas, separate them with spaces. Make sure you have a space before the first item and a space after the last item.

Then you can search for ' BB1 ' with a space before and after. You may not need with wildcards.

Lewis