+ Reply to Thread
Results 1 to 7 of 7

Unique ID for the same serial nos

  1. #1
    Registered User
    Join Date
    08-18-2012
    Location
    Dubai
    MS-Off Ver
    Excel 2003
    Posts
    4

    Unique ID for the same serial nos

    Hi Guys,

    I have doubts,regarding the unique serial nos..

    for example,i have lot of serial nosl like 4562,4665

    456987
    456559
    456559
    456987
    125467
    453658
    123456
    123456
    123456

    i need to generat the unique serial on the second 2nd row to create the unique id for these serial nos..

    for example
    456987---A1
    456559---A2
    456559---A3
    456987---A4
    125467---A5
    453658---A6
    123456---A7---2-jun-11
    123456---B7----3-jul-11
    123456---C7----4-aug-11

    if i need 123456 nos means it will come all three serial which unique id..

    SAme serial nos comes on different months..

    i need to identify the units comes in which month...based on unique id..

    iam struggling for long time to get the solution..

    i anyone can help me....

    appreciated your kind response..

    Thanks,
    Paul

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,386

    Re: Unique ID for the same serial nos

    Hi, and welcome to the forum

    Your request is not clear - at least to me.

    Please upload your workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show both a before and after situation and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    08-18-2012
    Location
    Dubai
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Unique ID for the same serial nos

    Quote Originally Posted by Richard Buttrey View Post
    Hi, and welcome to the forum

    Your request is not clear - at least to me.

    Please upload your workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show both a before and after situation and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.
    Thanks for advice..

    i will explain you clearly...

    Serial no date parts status
    09354 11-Aug-11 tested delivered
    33891 12-Aug-11 na fail
    74610 13-Aug-11 na fail
    12265 14-Aug-11 na delivered
    90697 15-Aug-11 tested fail
    72154 16-Aug-11 tested delivered
    85408 17-Aug-11 tested fail
    08481 18-Aug-11 nil fail
    07184 19-Aug-11 bf delivered
    55500 20-Aug-11 gh fail
    19767 21-Aug-11 ghj delivered
    19767 21-Jul-11 ght delivered
    19767 19-Oct-11 hg pending
    07184 22-Jan-12 th fail

    If i need to prepare the report for the above units status...

    for example 19767 serial nos we have tested these box 3 times...
    1st time data will be 19767 21-Aug-11 ghj delivered
    2nd time data will be 19767 21-Jul-11 ght delivered
    3rd time data will be 19767 19-Oct-11 hg pending

    normally in this data we are entering the serial nos and tested status..

    when iam going to prepare the report for the same serial nos with diffrent status...

    so i need to create unique id for the same serial nos which has tested 3 times...

    when i enter the serial nos it will show all the three line datas...

    like...19767

    19767 21-Aug-11 ghj delivered
    19767 21-Jul-11 ght delivered
    19767 19-Oct-11 hg pending

    i tried in vlookup but it will take the first serial nos only..

    Appreciate your kind response..

    thanks,

    regards,
    Paul

  4. #4
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Unique ID for the same serial nos

    How about something like this.
    Please Login or Register  to view this content.
    If the cell underneath is the same as the top, it adds the row number & divides it by 100 giving you a unique ID
    I've attached an example
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-18-2012
    Location
    Dubai
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Unique ID for the same serial nos

    Quote Originally Posted by Philb1 View Post
    How about something like this.
    Please Login or Register  to view this content.
    If the cell underneath is the same as the top, it adds the row number & divides it by 100 giving you a unique ID
    I've attached an example
    Thanks,

    i need to compare above all the serial nos,it not above 1 cell or below 1 cell,

    i may be top2 ,top 4 or top10 ,need to compare above all serial nos...

  6. #6
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Unique ID for the same serial nos

    If you sort the serial numbers into numerical order, it will compare all the numbers that are the same & give a unique ID
    Are you using separate cells? Or is all the info in one cell

  7. #7
    Registered User
    Join Date
    08-18-2012
    Location
    Dubai
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Unique ID for the same serial nos

    Hi...

    Thanks..

    but iam using single excel sheet which contain all data...based on this excel sheet..i need to prepare report for each and every serial nos...

    for example i will take one serial no like 23456 in this serial contain 4 different kind of data s mentioned below the reference..

    23456-------1/jan/2012------tested------new software-----delivered----2/jan/2012

    23456-------1/jul/2012------tested------new software-----delivered----2/jul/2012

    23456-------1/AUG/2012------tested------new software-----delivered----2/aug/2012

    23456-------1/SEP/2012------tested------new software-----delivered----2/sep/2012

    In that report sheet,,i will use to get this detail using VLOOKUP formula means it will get 1 st data only like...

    234568***************23456-------1/jan/2012------tested------new software-----delivered----2/jan/2012

    i want all four data which is matching these serial no...(23456)...

    similar...i want to put the unique id for these serial no ..i will use vlookup formula...


    unique id SER123***234568-------------1/jan/2012------tested------new software-----delivered----2/jan/2012...


    appreciated you king assistance.....

    Thanks,
    Paul...

+ 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