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

Originally Posted by Richard Buttrey

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

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

Originally Posted by Philb1

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

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

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