Matching IDs to a Database

[Jveto]

Hey all, looking for a bit of help. I'm starting to read into VBA, but I'm still unsure of when I should use it. Could I find another way to do this using excel functions?

Lets say for example, I have a database, about 2000 rows. Column A is a ID #, and column B is a description. I then have a separate excel sheet, that has only descriptions. I want to scan the database, and paste the ID # in to match the descriptions in the separate excel sheet.

Examples of descriptions:

Tall red 42 car
Small red 42 car
Tall blue 21 truck
Tall red 81 car

There are many shared words between descriptions.

In the database sheet, the description is in one column. However, for the separate search sheet, the descriptions might be broken up so each word has it's own column. I can either format the search sheet so that the descriptions end up in one cell, or write the code so that it searches each column.

My idea for what needs to happen :
Let's us assume I made the search sheet so the description is in one cell.
Break up the description into individual strings.
Search for the first string for every match in the database.
Search for the second string for a match in all the previous matches.
Continue this process until you finish all the strings.
If there was a single complete match, paste the ID# in the search sheet next to the description.
If there was more then one complete match, paste each match ID#, as well as highlight that cell yellow.
If there were partial matches, paste the ID#s and highlight orange. (this step I may get rid of.)
If there were no matches, leave the ID# cell blank, and highlight red.

This would continue until you reached the end of the search terms.

Any advice appreciated.
Thanks.

[dflak]

You may get a better response if you attach a sample file along with the description.

I'm not quite sure of the requirement, so I am not quite sure to the solution. On the one hand, it sounds like what you need to do is create what I call a Composite Key. A composite key is simply a concatenation of elements that makes a record unique. For example [ Size ] & ":" & [ Color ] & ":" [ Number ] & ":" & [ Type ] and do your matches against that.

On the other hand, it sounds like what you want is cascading drop-down lists. This article will get you started: http://www.utteraccess.com/wiki/Casc...ists_%28VBA%29.

Or maybe you need a combination of both.

[Jveto]

Hmm, reading over the cascading drop-down lists, it sounds like it's kind of like a funnel? Things need to be entered in that order or else they wont work.

Uploading my two excel sheets. One thing I may not have mentioned - The database might have extra descriptions, and the search sheet might be more bare descriptions. So for example, the database contains a "speed" in it, where as the search sheet doesn't have that.

I feel like I could write this code in C++, but I don't know how to incorporate C++ and excel... yet

[dflak]

Now that you mention it, there is a thing called Slicers which do filter based on what is selected in other slicers.

In my case, I work with wireless devices. An Manufacturer has multiple models and these models can be divided out by operating system. So If I select LG in the Manufacturer slicer, I get only LG models available in the Model slicer. If I then select Android as an O/S, I get only LG Android models in the Model slicer. If I then deselect LG to all Manufacturers, I get all Android models regardless of who makes them. The selection constricts the other data but unlike the cascading list, the order of the selection is irrelevant.

Does this sound like what you are looking for? Slicers work with Excel Tables and Pivot Tables. In fact, with the pivot tables, they can be used to set the filters on multiple tables at once (provided that they are all built from the same data source). Also they look pretty cool.

[Jveto]

That sounds like it could work for my application.

Would it still be ok if search terms have different amount of terms in them? So say they can range from 1 value to 6.

[dflak]

It will probably work. I could do more than speculate if you can provide a sample workbook so I can see how your data is organized. Change names or numbers to make the data non-sensitive.