Generate ID

[DasOliver]

If I have the following:
[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]


- How can make the ID I wrote on column "F" to appear in automatically after filling the first 5 columns?
- How can I avoid repeating the number, or in other words, is excel capable to provide the following number?

I will buy you a soda if you can help me with this!

Anyway, I will really appreciate your help if you can help!

Thanks in advance!

Oliver

 

[tusharm]

This is essentially a duplicate of what you posted 7 minutes earlier.

See http://www.mrexcel.com/forum/showthread.php?648790-Auto-Number-in-Excel

If I have the following:
[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]


- How can make the ID I wrote on column "F" to appear in automatically after filling the first 5 columns?
- How can I avoid repeating the number, or in other words, is excel capable to provide the following number?

I will buy you a soda if you can help me with this!

Anyway, I will really appreciate your help if you can help!

Thanks in advance!

Oliver

 

[DasOliver]

This explanation is better, it shows the need for avoiding duplicate values at to initiate counting each time any of the first 5 letters change, is this possible?

 

[Marcelo Branco]

Maybe

Select a cell in row 2, say, F2

Assumes sheetname = Sheet1. Adjust to suit

Formulas > Name Manager > New
Name: FirstLetters
Refers to: =LEFT(Sheet1!$A2)&LEFT(Sheet1!$B2)&LEFT(Sheet1!$C2)&LEFT(Sheet1!$D2)&LEFT(Sheet1!$E2)&"-"
Ok


In F2 enter this formula
=FirstLetters&TEXT(COUNTIF(F$1:F1,"*"&FirstLetters&"*")+1,"0000")
copy down

M.

 

[Marcelo Branco]

This is essentially a duplicate of what you posted 7 minutes earlier.

See http://www.mrexcel.com/forum/showthread.php?648790-Auto-Number-in-Excel

Hadn't seen this...

EDIT
My formula is basically the same of Tusharm's in the other post...

M.

 

[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