Excel 2007 : Matching Stock Codes - SOLVED :)

**SteveR2010** · 10-20-2010, 07:04 AM

Good Afternoon All,

Just wondering if I could have a moment of your time to help me with my problem! It is a little difficult to explain. I would appreciate any help with my problem.

We are a distribution company - we buy our products from China (VT-SZ) and then sell these within the European market. However along the way, when developing our systems (VT-UK) our stock codes have never been matched; between our own and in China.

This is causing us some issues as it is difficult to track incoming orders.

I have attached an excel spreadsheet. The first page shows stock codes from China (VT-SZ) and the second page shows the stock codes from us, VT-UK.
I am trying to match these two stock codes, so that our stock codes (VT-UK) and China's (VT-SZ) collerate.

Unfortunately we are using slightly different material descriptions for the same product; which makes any vlookup difficult to accomplish. I have tried braking down the stock descriptions to its basic form and then matching these up, but is proving more difficult than I first thought.

My question is, can you shed any light as to how to match up our stock codes, without having to go through each line by hand?

I have completed one item for example (line 197):

VT-UK Stock Codes VT-SZ Stock Code Material Type
1610 47LA04HHN1824 VT-47 core 0.10mm & 18/18 HTE 18"G*24"

Any questions or queries please let me know.

Kindest Regards,
Steve

**Marcol** · 10-20-2010, 09:45 AM

Hmmm....? Me thinks you have a problem on your hands!!

I can't see that there can be a one formula fits all solution for this one.
Try variations of this

Please Login or Register  to view this content.

Based on your one worked example this returns a result for 172 out of your 791 items and I cannot vouch for there not being duplicates/errors in the result.

If #VALUE! is returned then the formula has failed, if it returns #N/A when used with this lookup formula

Please Login or Register  to view this content.

Then it might be an error in converting mm to thous (1/25.4)

Please Login or Register  to view this content.

or there might not be a match, or the formula doesn't apply to that item, I have no way of knowing which.

Trim() might help in some cases.

You could copy out the rows with potential solutions, then build a similar formula to sift the remaining items, and repeat until it is easy to manaully fix the remainder.

Hope this is of some little help.

P.S. will post example soon, there seems to be a problem attaching the file at the moment.
P.P.S had to zip the file although the file is only 785kb....

**SteveR2010** · 10-20-2010, 10:31 AM

Many thanks Marcol,

I looked through your spreadsheet, and the working stock codes match perfectly thank you.

Where there is an error, how would you suggest I manipulate the data?

Do I need to change the descriptions so that they match up?

Many thanks for your time.

Kind Regards,
Steve

**Marcol** · 10-20-2010, 11:11 AM

I'll try to find another example that might work, and post another workbook to try to explain my thinking.

Might be a little while I have to go soon.

**Marcol** · 10-20-2010, 01:12 PM

This is going to take time no matter how you approach it.

All I can suggest is that you copy the unsucessful results to a separate sheet, then delete them from the main sheet, then repeat the procedure with another function.

I tried another function using nested substitute functions based on codes beginning "VT-901PP " and similar, but only found another 12 hits, there must be more in these groups, but I don't know the rules to get more hits
.
Some similar codes end with "Tooling" and others have percentages that don't mach your data base.

You should have a better feel for the rules than me,

This is what I used as a second "sift"

Please Login or Register  to view this content.

Sorry I can't be of more help.

**SteveR2010** · 10-21-2010, 05:37 AM

Hi Marco,

Many thanks for your help. I have managed to get almost 70% of the stocks matching up using your formuale.

I have had to go through some of the fields and edit them by hand, but you really have saved me countless hours!

And I learnt something new :D

Many thanks again,
Steve

**Marcol** · 10-21-2010, 06:33 AM

Happy to have been of help, only sorry I couldn't find a more general solution, but I'm sure you will get it down to a manageable level when manual matching is the easiest option.