Check a text string against a predefined list and place result in another sheet

[certifiedinsane]

Hi all,

I have a task, which may be best suited to doing in VBA but I'm not sure.

Scenario, I have a workbook with 3 sheets in it as follows

Sheet 1 is a list of products from a customer and their current quantity.
Sheet 2 is a list of my internal product codes. The codes are in the format (AAA A00 1234), each part is in a separate cell.
Sheet 3 is blank

Basically the data I get from the customer (sheet 1) is typically in a mixture of formats. I only need the specific item code but it is sometimes broken up in amongst a product description.
I want to look at the string, compare it against the list on sheet 2 and match the relevant parts of the product code then copy that code and the quantity from sheet 1 in to sheet 3 so I have a cleaned up list.

example:

Sheet 1
Cell A1 contains "1x AAA with random description A00 with more blah 1234"

Sheet 2 has a list like this with several permutations of the codes.
A1 AAA B1 A00 C1 1234
A2 BBB B2 A01 C2 5678

I want to look at Sheet 1, Cell A1 and check if it contains the string in Sheet 2,Cell A1, if it does the put AAA in to the first available cell in Sheet 3. Recheck the same string for the next segment and so on until I have the complete code in Sheet 3.

What's the simplest way to do this? Any ideas?

Thanks in advance..

[dilipandey]

Hi certifiedinsane,

would be helpful in you could upload a sample workbook along with your expected results. Thanks.


Regards,
DILIPandey
<click on below * if this helps>

[certifiedinsane]

Hi DILIPandey,

I'll upload a sample shortly. Thanks in advance.

[certifiedinsane]

Does this help?

sample.xlsx

[dilipandey]

OKay.. try using below formula in sheet 3 :-

Please Login or Register  to view this content.

Regards,
DILIPandey
<click on below * if this helps>

[certifiedinsane]

Hi ,

Thanks but it doesn't really do what I need.

I need to be able to look at the text in sheet 1. Pick out the product code bits from in amongst the description, ignoring anything that isn't a product code and match it to a specific code on sheet 2.

[dilipandey]

Okay.. can you show me an sample? Thanks.



Regards,
DILIPandey
<click on below * if this helps>

[windknife]

If the format of your product code is fixed, maybe this is right.

Sheet3!A2

Please Login or Register  to view this content.

Sheet3!B2

Please Login or Register  to view this content.

Committed with CSE
HTH,
WindKnife

[Debraj Roy]

FuzzyVlookUP is best in this scenarion..

Courtesy :Mr. Excel

Please check the attached File & If required UDF FUNCTIONS too..

FuzzyLookUP.xlsm