+ Reply to Thread
Results 1 to 12 of 12

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

  1. #1
    Registered User
    Join Date
    10-28-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    6

    Talking 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?
    Attached Files Attached Files
    Last edited by saranorton; 10-28-2011 at 11:12 AM.

  2. #2
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Help required by an expert excel programmer.

    (Deleted post until thread title changed)

    EDIT: Post title changed, see my next post
    Last edited by tigeravatar; 10-28-2011 at 11:18 AM. Reason: Thread title changed, see next post
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    10-28-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    6

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

    Dear tiger avatar,

    i have changed the thread title and attached a sample document as well.. please help me out.

  4. #4
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    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.
    Attached Files Attached Files

  5. #5
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    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. #6
    Registered User
    Join Date
    10-28-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    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. #7
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    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. #8
    Registered User
    Join Date
    10-28-2011
    Location
    New York, USA
    MS-Off Ver
    Excel 2007
    Posts
    6

    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. #9
    Registered User
    Join Date
    12-02-2013
    Location
    KUWAIT
    MS-Off Ver
    Excel 2007
    Posts
    1

    Thumbs up Re: Formula to insert serial numbers automatically to an excel sheet with diff. items

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

  10. #10
    Registered User
    Join Date
    03-10-2015
    Location
    Manila, Philippines
    MS-Off Ver
    2007
    Posts
    4

    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. #11
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,975

    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.

    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.
    Ben Van Johnson

  12. #12
    Registered User
    Join Date
    03-10-2015
    Location
    Manila, Philippines
    MS-Off Ver
    2007
    Posts
    4

    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...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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