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:

	A	B	C	D	E	F
1	Country	State	City	Submarket	Use	ID
2	Mexico	Baja California	Tijuana	Florido	Industrial	MBTFI-0001
3	Mexico	Baja California	Tijuana	Florido	Industrial	MBTFI-0002
4	Mexico	Baja California	Tijuana	Pacifico	Industrial	MBTPI-0001
5	Mexico	Baja California	Tijuana	Pacifico	Industrial	MBTPI-0002
6	Mexico	Baja California	Tijuana	Pacifico	Comercial	MBTPC-0001
7	Mexico	Baja California	Tijuana	Pacifico	Retail	MBTPR-0001
8	Mexico	Baja California	Tijuana	Pacifico	Retail	MBTPR-0002
9	Mexico	Baja California	Tijuana	Pacifico	Retail	MBTPR-0003
10	USA	California	San Diego	Otay	Retail	UCSOR-0001
11	USA	California	San Diego	Otay	Retail	UCSOR-0002
12	USA	California	San Diego	Otay	Retail	UCSOR-0003
13	Bolivia	Capital	La Paz	Centro	Industrial	BCLCI-0001
14	Bolivia	Capital	La Paz	Centro	Industrial	BCLCI-0002

<tbody>
</tbody>

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.