Guys, I have a student record excell sheet.

A1 heading= Student ID, in the format of first three letters of last name and first three letters of first name..

However, i want to add a unique number on to the end, to ensure when a sitatuin occurs when two students can have the same ID, it will not effect the linked database.

So i want a formula which will give me, for example if my name is jack powers, i currently have the formula which gives me

powjac, i want a formula which will search the table for a duplicate id of powjac and if,

there is a duplicate put 02, 03, and so on for each person with same name\

there ISNT a duplicate to put 01.

Thanks guys

On Thu, 15 Mar 2012 23:58:50 +0000, jeck wrote:


Guys, I have a student record excell sheet.

A1 heading= Student ID, in the format of first three letters of last
name and first three letters of first name..

However, i want to add a unique number on to the end, to ensure when a
sitatuin occurs when two students can have the same ID, it will not
effect the linked database.

So i want a formula which will give me, for example if my name is jack
powers, i currently have the formula which gives me

powjac, i want a formula which will search the table for a duplicate id
of powjac and if,

there is a duplicate put 02, 03, and so on for each person with same
name\

there ISNT a duplicate to put 01.

Thanks guys

You can use COUNTIF, with the criteria_range being $A$1:An where n is the row just above the cell being tested; and concatenate that with your ID.

If your Last Names were in Column B, and your first names in Column C, the formula might look like:

A2: =LEFT(B2,3) & LEFT(C2,3) & 1+COUNTIF($A$1:A1,LEFT(B2,3)&LEFT(C2,3))

or, perhaps,

A2: =LOWER(LEFT(B2,3) & LEFT(C2,3) & 1+COUNTIF($A$1:A1,LEFT(B2,3)&LEFT(C2,3)))

jeck presented the following explanation :
Guys, I have a student record excell sheet.

A1 heading= Student ID, in the format of first three letters of last
name and first three letters of first name..

However, i want to add a unique number on to the end, to ensure when a
sitatuin occurs when two students can have the same ID, it will not
effect the linked database.

So i want a formula which will give me, for example if my name is jack
powers, i currently have the formula which gives me

powjac, i want a formula which will search the table for a duplicate id
of powjac and if,

there is a duplicate put 02, 03, and so on for each person with same
name\

there ISNT a duplicate to put 01.

Thanks guys

I'd simply append to the ID the row number, or D.O.B. serial, or S.I.N.
so as to avoid any ambiguity. Most 'schools' use a structured numbering
system to assign StudentID that does not have anything to do with their
name. My Student Grades Manager addin uses whatever school system
scheme is being implemented by the institution using the addin. This is
usually a structured ID numbering system something like I mentioned.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion