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.

 generating Unique ID numbers for students
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## generating Unique ID numbers for students

#1
November 5th 04, 05:14 PM
 Maltenrazer external usenet poster 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, 05:38 PM
 Niek Otten external usenet poster 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, 12:32 AM
 hgrove external usenet poster 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

 Thread Tools Display Modes Linear Mode

 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 User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 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 11:26 PM.