View Single Post
  #3   Report Post  
hgrove
 
Posts: n/a
Default


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
View this thread: http://www.excelforum.com/showthread...hreadid=275688