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)))