Help required by an expert excel programmer.

[saranorton]

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?

 

[BobUmlas]

Assuming you have titles in row 1, and that column A contains the item description, this formula in in row2 filled down, works:

=UPPER(LEFT(A2,5))&TEXT(COUNTIF($A$2:A2,A2),"00000")

Bob Umlas
Excel MVP

-check out my new book at http://www.mrexcel.com/outsidethebox.html