increment value in cells

Ok, i feel real stupid now, hopefully some one can help me out....

I want to use the increment function in excel,lets say from 1 till 5, i select te first cell end drag down till whatever number.
That works fine and is no issue.

However i am changing part numbers >1000 from old format to new format.
Example would be :

Product x (old partnumber) 100.00001 (new format) E010006.00.00.00 so far so good.

a colleague has had the bright idea to place all the options for that one product in the same format.
so what you would get is:
option 1 product x E010006.00.01.00
option 2 product x E010006.00.02.00

And so on....

if i use that drag option the last digit will increment or makes e.g E010006.00.01.01 out of the code.

Can any one tell me if it is possible to increment the XX part in : E010006.00.XX.00


(maybe small explanation of the code E0AAAA.BB.CC.DD there AAAA is the product, BB is the revision of the product, CC is the option, DD revision of the option)

Thanks!

Thanks,

I am not completely sure if the formula would do in my case
exc.jpg

column a is the old number, column b the new number.
The blue cells would be the main product, with below them the options.

I can generate the first numbers easily but it would be nice if i can automate the increment of the options (some machines have 30+ options) and typing them all in would be asking for mistakes....

ARRAY formula in D3, then drag down. Pl see file attached
ARRAY formula should be confirmed with Ctrl+shift+Enter keys together.

Please Login or Register  to view this content.

thanks with all the help so far!

I have uploaded an example file, i am looking for a quick fix, if the quick fix is to enter the new numbers manually it is

So the background information:

the old article numbers are e.g 010.00001 (where 010 is a productgroup and 00001 well you need to start somewhere right )
the new format will be:
AABBCCCDDEEF
AA product range
BB product group
CCC Machine type in product group
DD machine revision
EE option on machine
F option revision

We have multiple ranges, A1 top notch B1 "okísh" C1 "stuff that you buy if you cannot afford the other ranges.."

The idea is that all options that can go on a main product get a partnumber related to the main product.
Let me get you a quick example:

Current situation:
Product 1 article number is 010.00001 and it can have option 1 100.00001 and option 2 100.00002.
Product 2 article number is 010.00002 and it can have option 1 100.00001 and option 3 100.00003.

new situation:
Product 1 new article number E010001.00.00.0
Option 1 (for product 1) E010001.00.01.0
Option 2 (for product 1) E010001.00.02.0

Product 2 new article number E010002.00.00.0
Option 1 (for product 1) E010002.00.01.0
Option 3 (for product 1) E010002.00.02.0


Does it make more sense?? the problem with our quoting program is that it can not link options to products and by using these numbers you can enter a query on lets say E010002 and you will find all options on that machine.


So what i am looking for? a quick way to get those option numbers in without the possibility of making human errors, i do not mind entering the main article numbers but as said before some products can have 50+ options and we are looking at thousands of partnumbers here....



Any help is highly appreciated!

it helps when you add the file.....

Originally Posted by kvsrinivasamurthy

ARRAY formula in D3, then drag down. Pl see file attached
ARRAY formula should be confirmed with Ctrl+shift+Enter keys together.

Please Login or Register  to view this content.

This is the solution, works like a charm!!! Many Many thanks!!!!!