Auto Number in Excel

[DasOliver]

I have been trying to "auto number" property ID's

I am putting a database of properties all over the world and can't find a way to autonumber based on property criteria.

For example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Mexico[/TD]
[TD]Tijuana[/TD]
[TD]Florido[/TD]
[TD]Industrial[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Argentina[/TD]
[TD]Buenos Aires[/TD]
[TD]Belloso[/TD]
[TD]Comercial[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ecuador[/TD]
[TD]Quito[/TD]
[TD]Riestra[/TD]
[TD]Industrial[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mexico[/TD]
[TD]Guadalajara[/TD]
[TD]Sureste[/TD]
[TD]Industrial[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Chula Vista[/TD]
[TD]Otay[/TD]
[TD]Comercial[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I need a formula that places in the following field, for example:

MTFI-0001
ABBC-0001
EQRI-0001
MGSI-0001
UCOC-0001

Incremental in the number must be when a property meets the same criteria, that way the end will be "0002" in each case above.

I would like that Excel can display which is the next number following once the criteria is resumed, is this possible?

Thanks
Oliver

 

[tusharm]

Using an additional column to simplify the analysis...

Suppose your data are in A:D starting with row 2 (row 1 being a header row).

Then in E2 enter the formula =LEFT(A2,1)&LEFT(B2,1)&LEFT(C2,1)&LEFT(D2,1)

In F2 the formula =E2&"-"&TEXT(COUNTIF($E$2:E2,E2),"0000")

Copy E2:F2 as far down as you have data in A:D.

I have been trying to "auto number" property ID's

I am putting a database of properties all over the world and can't find a way to autonumber based on property criteria.

For example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Mexico[/TD]
[TD]Tijuana[/TD]
[TD]Florido[/TD]
[TD]Industrial[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Argentina[/TD]
[TD]Buenos Aires[/TD]
[TD]Belloso[/TD]
[TD]Comercial[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ecuador[/TD]
[TD]Quito[/TD]
[TD]Riestra[/TD]
[TD]Industrial[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mexico[/TD]
[TD]Guadalajara[/TD]
[TD]Sureste[/TD]
[TD]Industrial[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Chula Vista[/TD]
[TD]Otay[/TD]
[TD]Comercial[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I need a formula that places in the following field, for example:

MTFI-0001
ABBC-0001
EQRI-0001
MGSI-0001
UCOC-0001

Incremental in the number must be when a property meets the same criteria, that way the end will be "0002" in each case above.

I would like that Excel can display which is the next number following once the criteria is resumed, is this possible?

Thanks
Oliver

 

[DasOliver]

Thank you, is there a way to avoid continuance of numbering if the first for letters change? if fact to begin counting 0001 ?

 

[tusharm]

If any combination of the 1st letters (in the l-to-r sequence) is unique, it will get a value of 0001.

In fact, I imagine it would be easier to check the result then ask the question and wait for someone to respond. {grin}

Thank you, is there a way to avoid continuance of numbering if the first for letters change? if fact to begin counting 0001 ?

 

[tusharm]

If any combination of the 1st letters (in the l-to-r sequence) is unique, it will get a value of 0001.

In fact, I imagine it would be easier to check the result then ask the question and wait for someone to respond. {grin}

Thank you, is there a way to avoid continuance of numbering if the first for letters change? if fact to begin counting 0001 ?