Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Excel combo box to display list of partial matches (any position)

Posted on 2014-11-19
11
352 Views
Last Modified: 2014-11-20
Hello Experts
I would like to add a combox box that will return partial matches similar to Excel's "contains" table filter.

The user reads the Product name, then clicks on the adjacent Material column, and enters any text string they want. The drop down box will contain all the records from the Material sheet that match

Thanks for you help!
0
Comment
Question by:tomfolinsbee
  • 6
  • 5
11 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40453585
What do you mean by "The user reads the Product name"? Is the "Material Column" on the "Material" sheet?

Your question would be easier to answer if you could post your workbook.
0
 

Author Comment

by:tomfolinsbee
ID: 40453949
Martin,

I rewrote the question for clarity.

Workbook has two sheets, Product and Material, each with one column of about 1500 unique text records. NameA and NameB. There are some exact matches between NameA and NameB, but generally, they are similar, with some variations, usually in the middle of the string. Objective is to help the user to find a single NameB record that corresponds to each NameA.

The user will select a record in the NameA column, and in the adjacent column, type in a text string. The application will display a dropdown list with all the partial matches in NameB, similar to Excel's "contains" option in the Data - Filter menu, ie, the matches can in any part of NameB, not just first few characters.

Prefer Office 365 version of Excel since multiple people will be responsible for working on the matching from a remote location (but not concurrently).
Test-EE-20141119.xlsx
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40453962
I can only give you an Excel 2010 based solution but I can generate it in xls format which I assume (but don't know for sure) that Office 365 can open.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:tomfolinsbee
ID: 40453969
Thank you!
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40454082
I'm working on this and I realized that I don't understand what "select[ing] a record in the NameA column" has to do with anything? Don't you just want the list to show all the Material sheet values that contain what the user types in?
0
 

Author Comment

by:tomfolinsbee
ID: 40454113
Yes, you are right.  
I just meant that the user decides which row to work on...
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40454116
Please verify that you want the combobox to show entries from the Materials sheet that contain the string typed into the combobox no matter which row of column B is selected.
0
 

Author Comment

by:tomfolinsbee
ID: 40454232
Correct, thanks.
0
 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40454388
I'm not very happy with this solution and you can ignore it if you want to.

In order to get the data validation list to show the data you want you need to
1.    type some string into column C
2.    select any other cell
3.    reselect the column C cell
4.    click the down-arrow.

I'll continue to work on it and let you know if I can do better.
Q-28565315.xlsm
0
 

Author Closing Comment

by:tomfolinsbee
ID: 40455166
Martin, it works really well. Changing the focus in order to populate the list isn't a problem. Very happy that we can now do mid-string matches... it's a big improvement. Thanks for your efforts!
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40455623
I like to keep my customers satisfied so here's an improvement where steps 2 to 4 above aren't needed. The previous version was also creating a list of matching strings in column B of the Material sheet to be used as a Named Range for the data validation. In this version I changed that column to ZZ to get it out of view. Note that if columns are added or deleted on the Material sheet that the column will no longer be ZZ, but the code will handle that. The code is based on my Magical Floating ActiveX Control article and if you find it helpful you can click the "Vote this article as helpful" button.

In any case you're welcome and I'm glad I was able to help.

In my profile you'll find links to some other articles I've written that may interest you.
Marty - MVP 2009 to 2014
Q-28565315-2.xlsm
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question