Search for Keywords and Return the Appropriate Item

Hi everyone. I am having a really tough time figuring out whether this could be done or not, and I am just lost. Basically, we often get mobile phone orders in Excel format but the items listed are in a different format from the ones we have in our inventory system.

Please refer to the attachment.

In the Item List tab, you will find under the Item List column, a list of phones as they are named in our inventory system.

However, in the Order list tab, you will see a list of odered phones that are either named in a different format or are a different variant of the same phone on the Inventory Item list.

So what I did was create keywords that are associated with a specific phone. So for example, for HTC Touch Dual - P5310 (cell A7 in the Item List tab) , it's associated keywords are:

P5310 , Touch Dual , and Neon 400 . HTC is the Manufacturer.

Now in cell A4 of the Order List tab, it says HTC P5310 SIL . This should therefore be assigned HTC Touch Dual - P5310 as per the inventory item list.

So basically, I was hoping for some formula or macro that would look at the ordered item, then check if it contains the manufacturer name, then check if it contains any one of the keywords, then return the associated inventory item to the cell beside the order item.

So for HTC P5310 SIL in cell A4 in the Order List tab, it sees that it's manufacturer is HTC ... so now check if it contains any of the keywords. Since it contains P5310, then it has identified that it is an HTC Touch Dual - P5310 as per the inventory item list. And vice versa. The result should be something like in the Result tab.

I hope I'm not being confusing. Anyways, any assistance would be much appreciated. This is a huge problem for me and I'm getting really really desperate.

Try the attached

Originally Posted by millz

Try the attached

Holy smokes, looks like it works. I will try it with a larger dataset tomorrow. Awesome awesome! Thank you. Just one question ... is there a way to make it so that doesn't matter whether lower or upper case?

This should ignore casing now

This should do what you want and faster when you have bulky data.

Originally Posted by millz

This should ignore casing now

Thank you millz. It works great. I've hit another snag though. I feel terrible for bringing it up.

Is there a way so that the macro recognizes the keyword if it is within a text string.

So for example, say the Inventory Item is "HTC X710A Raider LTE" with a keyword "X710A" .

But say, the Order Item is "HTC Raider(X710A)" .

Is there a way for the macro to still recognize the keyword "X710A" even though the order item has "(X710A)" instead ?

Originally Posted by jindon

This should do what you want and faster when you have bulky data.

Thank you Jindon. It works great. I've hit another snag though. I feel terrible for bringing it up.

Is there a way so that the macro recognizes the keyword if it is within a text string.

So for example, say the Inventory Item is "HTC X710A Raider LTE" with a keyword "X710A" .

But say, the Order Item is "HTC Raider(X710A)" .

Is there a way for the macro to still recognize the keyword "X710A" even though the order item has "(X710A)" instead ?

So what would be the logic to determine the keyword?

Or I should ask why it is not "HTC"?

The logic should be that first it should find the manufacturer (HTC). Then it looks for the 1st keyword (X710A). If the manufacturer and 1st keyword are there, then it has determined that the inventory item name should in fact be HTC X710A Raider LTE, as per the inventory item list.

If the 1st keyword is not found, then it looks for the 2nd keyword (say, Raider). If the manufacturer and 2nd keyword is there then it has determined that the inventory item name is, again, HTC X710A Raider LTE . And so on and so forth.

The problem is that an ordered item may contain a keyword but have something else attached, like a parenthesis. So an ordered item might look like HTC Raider (X710A) instead of just HTC Raider X710A. The keyword X710A is there, but it isn't recognized because there are parenthesis attached.

Then better to post a workbook with all the possible combination example data and the result that you want.

Originally Posted by jindon

Then better to post a workbook with all the possible combination example data and the result that you want.

Hi Jindon. I have attached a workbook. It also contains the macro that you posted. The desired result is in the Result tab. When I run the macro, it works really well, but hits a snag when it looks at cell A3 in the Order List tab.

Try change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Originally Posted by jindon

This should do what you want and faster when you have bulky data.

Hi Jindon. Sorry I have another question if you don't mind. Your code works perfect when the Order List items are in column A. Which part of the code should I modify so that it works if the Order List items are in column L? I just can't seem to figure it out.

Please Login or Register  to view this content.

Try replace all of a(i,2) with a(i,12)

Otherwise I need to see exact sheet layouts.

Originally Posted by jindon

Try replace all of a(i,2) with a(i,12)

Otherwise I need to see exact sheet layouts.

Hi Jindon. Thank you for your speedy reply. I did as you suggested but I get a runtime error 13 type mismatch and this piece of code gets highlighted:

Please Login or Register  to view this content.

I have attached the actual workbook I am using.

Is this how you wanted?

Please Login or Register  to view this content.

Works great. Thank you so much Jindon, you are a genius! I can only hope one day to be as good as you.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

I am too slow again.. but will post my code to see if I was on the right track..

I took a different approach.. and made it show multiple matches separated by a "/".

For example:
Samsung R351
showed (in column M)
Samsung R351 / SAMSUNG LINK SOLO R351 / SAMSUNG LINK R351

Please Login or Register  to view this content.

In a Module:

Please Login or Register  to view this content.