Remember Me?

#1
November 5th 04, 06:14 PM
 Maltenrazer Posts: n/a
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.

--
Maltenrazer
------------------------------------------------------------------------
Maltenrazer's Profile: http://www.excelforum.com/member.php...o&userid=16130

#2
November 5th 04, 06:38 PM
 Niek Otten Posts: n/a

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.

--
Maltenrazer
------------------------------------------------------------------------
Maltenrazer's Profile:
http://www.excelforum.com/member.php...o&userid=16130

#3
November 6th 04, 01:32 AM
 hgrove Posts: n/a

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post confused on the tundra Excel Discussion (Misc queries) 5 December 18th 04 11:19 PM Confused on the tundra Excel Discussion (Misc queries) 5 December 17th 04 08:33 PM BecG Excel Discussion (Misc queries) 1 December 8th 04 05:55 PM darebo Excel Discussion (Misc queries) 2 December 6th 04 06:01 PM Robert Judge Excel Worksheet Functions 3 November 5th 04 05:36 PM

All times are GMT +1. The time now is 06:14 AM.