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:

Mexico	Tijuana	Florido	Industrial
Argentina	Buenos Aires	Belloso	Comercial
Ecuador	Quito	Riestra	Industrial
Mexico	Guadalajara	Sureste	Industrial
USA	Chula Vista	Otay	Comercial

<tbody>
</tbody>

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:

Mexico	Tijuana	Florido	Industrial
Argentina	Buenos Aires	Belloso	Comercial
Ecuador	Quito	Riestra	Industrial
Mexico	Guadalajara	Sureste	Industrial
USA	Chula Vista	Otay	Comercial

<tbody>
</tbody>

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 ?