generating Unique ID numbers for students

[Maltenrazer]

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

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

so what i want to do is take the first 3 letter of a students name the
add 00 ,and then it should , generate numbers from 1-90 , than i coul
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 i
and then i want numbers from 1-90 to be inputed.

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

thank you for your help in advance.. :

 

[Niek Otten]

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

 

[hgrove]

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. Thi
assumes OP wants the serial number portion of the IDs not to reset whe
the partial name protion changes. If s/he does want resetting to 000
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 i
column A are sorted.

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

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

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