A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

generating Unique ID numbers for students



 
 
Thread Tools Display Modes
  #1  
Old November 5th 04, 05:14 PM
Maltenrazer
external usenet poster
 
Posts: n/a
Default generating Unique ID numbers for students


hi, i need help i am a college student, i am creating a spreadsheet for
my portfolio

i would like to generate ID numbers for students in a spreadsheet i am
doing.

so what i want to do is take the first 3 letter of a students name then
add 00 ,and then it should , generate numbers from 1-90 , than i could
use this as an id number.

e.g

*Dav*id

DAV0056

"dav" in the first part is from the name, the two zero are just put in
and then i want numbers from 1-90 to be inputed.

i have 90 students names that i need to create Unique ID numbers. so
please can you tell me the formula/function on how i can do this.

thank you for your help in advance..


--
Maltenrazer
------------------------------------------------------------------------
Maltenrazer's Profile: http://www.excelforum.com/member.php...o&userid=16130
View this thread: http://www.excelforum.com/showthread...hreadid=275688

Ads
  #2  
Old November 5th 04, 05:38 PM
Niek Otten
external usenet poster
 
Posts: n/a
Default

Let's suppose your names are in column A.
In B1, enter:
=LEFT(A1,3) and fill down
In C2, enter:
=IF(LEFT(A2,3)=B1,C1+1,0) and fill down
In D2, enter:
=B2&"00"&TEXT(C2,"00") and fill down


--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Maltenrazer" > wrote in message
...
>
> hi, i need help i am a college student, i am creating a spreadsheet for
> my portfolio
>
> i would like to generate ID numbers for students in a spreadsheet i am
> doing.
>
> so what i want to do is take the first 3 letter of a students name then
> add 00 ,and then it should , generate numbers from 1-90 , than i could
> use this as an id number.
>
> e.g
>
> *Dav*id
>
> DAV0056
>
> "dav" in the first part is from the name, the two zero are just put in
> and then i want numbers from 1-90 to be inputed.
>
> i have 90 students names that i need to create Unique ID numbers. so
> please can you tell me the formula/function on how i can do this.
>
> thank you for your help in advance..
>
>
> --
> Maltenrazer
> ------------------------------------------------------------------------
> Maltenrazer's Profile:
> http://www.excelforum.com/member.php...o&userid=16130
> View this thread: http://www.excelforum.com/showthread...hreadid=275688
>



  #3  
Old November 6th 04, 12:32 AM
hgrove
external usenet poster
 
Posts: n/a
Default


Niek Otten wrote...
>Let's suppose your names are in column A.
>In B1, enter:
>=LEFT(A1,3) and fill down
>In C2, enter:
>=IF(LEFT(A2,3)=B1,C1+1,0) and fill down
>In D2, enter:
>=B2&"00"&TEXT(C2,"00") and fill down

...

Um, why not just enter

D2:
=LEFT(A2,3)&TEXT(ROW()-1,"0000")

and fill down with no intermediate formulas in columns B or C. This
assumes OP wants the serial number portion of the IDs not to reset when
the partial name protion changes. If s/he does want resetting to 0001
rather than 0000, then

D2:
=LEFT(A2,3)&"0001"

D3:
=LEFT(A3,3)&TEXT(IF(LEFT(A3,3)=LEFT(A2,3),RIGHT(D2 ,4)+1,1),"0000")

and fill D3 down. Note that these latter formulas assume the names in
column A are sorted.

Note also that this entire exercise is pointless. If the sole purpose
is creating unique indentifiers, then using formulas like

D2:
=TEXT(ROW()-1,"0000")

would be sufficient. Many (most?) of the nastier referential integrity
problems stem from the misguided desire to make such identifiers
'friendly'. Why bother?


--
hgrove
------------------------------------------------------------------------
hgrove's Profile: http://www.excelforum.com/member.php...o&userid=11432
View this thread: http://www.excelforum.com/showthread...hreadid=275688

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorting when some numbers have a text suffix confused on the tundra Excel Discussion (Misc queries) 5 December 18th 04 10:19 PM
Sorting imported "numbers" Confused on the tundra Excel Discussion (Misc queries) 5 December 17th 04 07:33 PM
Paste rows of numbers from Word into single Excel cell BecG Excel Discussion (Misc queries) 1 December 8th 04 04:55 PM
Seed numbers for random number generation, uniform distribution darebo Excel Discussion (Misc queries) 2 December 6th 04 05:01 PM
How to Replace numbers and text with numbers only? Robert Judge Excel Worksheet Functions 3 November 5th 04 04:36 PM


All times are GMT +1. The time now is 03:21 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.