Generate ID's, difficult request.

[DasOliver]

Can someone help me get a way to generate ID's by avoiding duplicates and permanent values, unless changed manually.

Lets say I have:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Country[/TD]
[TD]State[/TD]
[TD]City[/TD]
[TD]Submarket[/TD]
[TD]Use[/TD]
[TD]ID[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Mexico[/TD]
[TD]Baja California[/TD]
[TD]Tijuana[/TD]
[TD]Florido[/TD]
[TD]Industrial[/TD]
[TD]MBTFI-0001[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Mexico[/TD]
[TD]Baja California[/TD]
[TD]Tijuana[/TD]
[TD]Florido[/TD]
[TD]Industrial[/TD]
[TD]MBTFI-0002[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Mexico[/TD]
[TD]Baja California[/TD]
[TD]Tijuana[/TD]
[TD]Pacifico[/TD]
[TD]Industrial[/TD]
[TD]MBTPI-0001[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Mexico[/TD]
[TD]Baja California[/TD]
[TD]Tijuana[/TD]
[TD]Pacifico[/TD]
[TD]Industrial[/TD]
[TD]MBTPI-0002[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Mexico[/TD]
[TD]Baja California[/TD]
[TD]Tijuana[/TD]
[TD]Pacifico[/TD]
[TD]Comercial[/TD]
[TD]MBTPC-0001[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Mexico[/TD]
[TD]Baja California[/TD]
[TD]Tijuana[/TD]
[TD]Pacifico[/TD]
[TD]Retail[/TD]
[TD]MBTPR-0001[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Mexico[/TD]
[TD]Baja California[/TD]
[TD]Tijuana[/TD]
[TD]Pacifico[/TD]
[TD]Retail[/TD]
[TD]MBTPR-0002[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Mexico[/TD]
[TD]Baja California[/TD]
[TD]Tijuana[/TD]
[TD]Pacifico[/TD]
[TD]Retail[/TD]
[TD]MBTPR-0003[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]USA[/TD]
[TD]California[/TD]
[TD]San Diego[/TD]
[TD]Otay[/TD]
[TD]Retail[/TD]
[TD]UCSOR-0001[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]USA[/TD]
[TD]California[/TD]
[TD]San Diego[/TD]
[TD]Otay[/TD]
[TD]Retail[/TD]
[TD]UCSOR-0002[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]USA[/TD]
[TD]California[/TD]
[TD]San Diego[/TD]
[TD]Otay[/TD]
[TD]Retail[/TD]
[TD]UCSOR-0003[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Bolivia[/TD]
[TD]Capital[/TD]
[TD]La Paz[/TD]
[TD]Centro[/TD]
[TD]Industrial[/TD]
[TD]BCLCI-0001[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Bolivia[/TD]
[TD]Capital[/TD]
[TD]La Paz[/TD]
[TD]Centro[/TD]
[TD]Industrial[/TD]
[TD]BCLCI-0002[/TD]
[/TR]
</tbody>[/TABLE]


I wrote column F, but my need is to populate automatically this column with a formula.

The formula must:
- renew numbering each time that any of the first 5 characters change; look at the sequence above, 0001, 0002, 0003 then on new characters it initiates again on 0001.
- Formula must not allow duplicate ID'S
- It would be ideal if the the formula can display the following sequenced number or those values that have been erased or moved because the status of the property changed.
- values must be permanent once inserted, it would be interconnected with other sheets and perhaps other software, so values can not be changed if a new row is inserted.

The above criteria will solve my needs, i will appreciate a lot if somebody has the knowledge of providing this formula to me.

Best regards,
Oliver

 

[lrobbo314]

I think this should do it

=left(a2,1)&left(b2,1)&left(c2,1)&left(d2,1)&left(e2,1)&"-"&rept("0",4-len(countifs($a$2:a2,a2,$d$2:d2,d2,$e$2:e2,e2)))&countifs($a$2:a2,a2,$d$2:d2,d2,$e$2:e2,e2)

 

[lrobbo314]

Or I guess if you really want to be thorough then it should look like this.

=LEFT(A2,1)&LEFT(B2,1)&LEFT(C2,1)&LEFT(D2,1)&LEFT(E2,1)&"-"&REPT("0",4-LEN(COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$C$2:C2,C2,$D$2:D2,D2,$E$2:E2,E2)))&COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$C$2:C2,C2,$D$2:D2,D2,$E$2:E2,E2)

 

[Mindpsyche]

Ok so you said that you typed in the values in column F? I could be wrong but try this:

In column F, maintain only the Text part i.e. MBTFI, UCSOR etc...then in column G try this formula: =F1&" - "&0&COUNTIF($F$1:F1,F1)

 

[DasOliver]

Thank you so much, it worked! I tried to get a formula for several days and today it happened! thanks again, I really appreciate your help!

 

[lrobbo314]

Cool! Happy to help.