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

1. ## 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?

2. ## Re: Help required by an expert excel programmer.

(Deleted post until thread title changed)

EDIT: Post title changed, see my next post

3. ## Re: Formula to insert serial numbers automatically to an excel sheet with diff. items

Dear tiger avatar,

4. ## Re: Formula to insert serial numbers automatically to an excel sheet with diff. items

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.

5. ## Re: Formula to insert serial numbers automatically to an excel sheet with diff. items

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

6. ## Re: Formula to insert serial numbers automatically to an excel sheet with diff. items

thankyou soo much..... can it be possible that the table is made in a separate file and link up the formula to look up from there?

7. ## Re: Formula to insert serial numbers automatically to an excel sheet with diff. items

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

8. ## Re: Formula to insert serial numbers automatically to an excel sheet with diff. items

hey.. thank u very very much for ur kind help.

9. ## Re: Formula to insert serial numbers automatically to an excel sheet with diff. items

Originally Posted by tigeravatar
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.
Thank you very much. This is very helpfull for me

10. ## Re: Formula to insert serial numbers automatically to an excel sheet with diff. items

the serial number is reseting the value when I delete a serial ex. CA0003, CA0004, CA0005 when I delete CA0005, after which I create new item and generate CA0005 again. How will it generate a unique value? thanks!

11. ## Re: Formula to insert serial numbers automatically to an excel sheet with diff. items

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.

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.

12. ## Re: Formula to insert serial numbers automatically to an excel sheet with diff. items

protonLeah

I'm sorry protonLeah, for not complying in the forum rule about the matter. I thought it can be a used to my need and if it's ok to raise the issue I encounter. My apology...

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1