Formula to insert serial numbers automatically to an excel sheet with diff. items

Hi,

I have an enormous inventory list on excel containing fields including date of purchase, item name, classification (as display, accessory, hardware, software, multimedia etc. etc.) and price. Each item is copied several times in the sheet in new row every time depending on quantity. For example if I have 100 Logitech keyboards in stock, there will be 100 ROWS showing it in stock at different places in the sheet.

NOW.. I want to allocate a unique SERIAL NUMBER for every item in every row. Like for Mouse, for eg, CAMOU00001, CAMOU00002…… and for keyboard, for eg, CAKYB00001, CAKYB00002……

The Problem is that everything is present in so huge quantity that I want to build up a formula that can just see the classification of the row, then find the last serial number for the same classification and add +1 to it.. it should be left blank If the classification in that row is blank. it will help me by just pasting the formula and saving time.

Can anyone help me out building up such a formula for allocating ALPHA-NUMERIC SERIAL NUMBERS to my inventory lists?

(Deleted post until thread title changed)

EDIT: Post title changed, see my next post

saranorton,

Attached is a modified version of your sample workbook. In columns AA:AB is the table of Sub Classifications and Abbreviations. Using that, cell A4 contains this formula:
=IF(B4="","","CA"&VLOOKUP(F4,$AA$4:$AB$19,2,FALSE)&TEXT(COUNTIF($F$4:F4,F4),"00000"))

And then its copied down.

Also, I removed the merged cells that were rows 3 and 4. There was no need for that, I just made row 3 higher and deleted the empty row 4. Generally speaking, try to avoid merged cells whenever possible

yes, you would only have to change the $AA$4:$AB$19 reference to wherever the table is stored. However, I would recommend keeping the table within the workbook that's using it if possible

Machme,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.