Managing 2 excel file to get product code

[atanu]

Hi! I am Atanu. I am working in a company dealing with manufacturing of mechanical spear parts. When we release a product for manufacturing we make drawing and given the same to the factory. To make the drawing handy we make an excel sheet we call “MC” which contains the summary of the drawing like type of product, tube length, type of component etc. I have to generate the product code based on the drawing or “MC” and then make the BOM in SAP. To make the code I have to use two excel files. One of these file contains all the basic code like xxx-xxx-xxxxx-0000 ( standard code). In the other file nonstandard part of the code is maintained (last 4 number of the code) it is maintained serially like 0001, 0002, 0003…… At present the nonstandard part is 0925. If the product is nonstandard then I have to combine the basic code and the nonstandard code and create the product code as xxx-xxx-xxxxx-xxxx. If the product is standard then I get the product code from the 1st file where the standard codes are maintained. If the product is nonstandard then the basic code I get from the 1st file (xxx-xxx-xxxxx- ) and the last 4 digit I have to get from 2nd file based on the drawing or “MC”. (Eg: xxx-xxx-xxxxx-0023) If the product dosenot matches with the old codes I have to enter new nonstandard code the next number 0926 and the product code will be xxx-xxx-xxxxx-0926. To make this code it takes about 80% of the total time to make BOM in SAP. After making this code I give the same to our authorized person to create the code in SAP . after he makes the code in SAP, I make the BOM of the material this part takes 20% of the time. Is there any softwate or programming like VB, C/C++ etc by which the code can be made automatically when we make “MC” using the two excel files for codification . I have tried excel formula like VLOOKUP, HLOOKUP or combining various formulas but they are not giving satisfactory result. Though standard part can be managed somehow, nonstandard cant be, specially when new code has to be entered.
Please help to solve this problem.
Thanks and regards,
Atanu

[Rabbit]

It's hard to follow what you're saying. Sample data and results would help.

[Guido Geurs]

Is it possible to attach example files in Bytes?
If the data is confidential, please replace it with representative data.
If the files are to large, set only +- 10 lines in the sheets so we can see the structure of the sheets and the type of data.
Also, if possible, add an example of the results you finally want so we can see in which direction we have to work.

[atanu]

OK, i am attaching sample excel files 3 nos for your reference. In the file name "SAMPLE-MC" the product summary from drawing given based on this I have to generate the code. I have given 2 example one is of standard product and other is of non standard product. the product may vary in product type, belt width, tube length, bld type, skt type, boot type, em type, no of arm. all the parameters may vary. based on the parameter there is a logic to search for the basic code. I have mentioned the same in the adjuscent column. in the row no, 26 the logical code is mentioned for respective product. then I copy the code go to the file name "STANDARD CODE" then find the copyed code by pressing CTRL+F >> CTRL+C >> ENTER. the product code will be the corrosponding SAP code. The product becomes nonstandard based on the tube length parameter. for nonstandard code all the above procedure will follow to get the basic code. then I go to the file "NON STANDARD CODIFICATION". Here I get the code by filtering the datas 1st with tube length, then with EM and then with offset height.

Hope this will clear all confusion.

regards,
Atanu

[NeoPa]

You have a complex procedure here, but your explanation, though it is quite full, leaves out many steps of explanation. Also, your files are missing titles to explain some of the information. EG. B1100X31N1PBS & B11200V2X2PCS are shown, but nothing says what they are, or where they fit into the picture. I expect these are important, so missing that out is a massive loss to any chance of understanding what you're trying to explain. This is just an example of why this should be explained more fully. It is not the only item where important information is missing. Analysis of a problem can only be carried out with all the information available and clear. You would need to make sure this is done before anyone can even start to help you. Looking through files that don't make sense is a long and frustrating business if the information in there is incomplete.

Don't get me wrong. You've made a decent stab at a very difficult explanation, but we still cannot help unless the information is all there and makes complete sense.