Creating a unique ID

[swati kanodia]

Hi,

I am trying to create a unique ID for agents registered with me using multiple criteria. Please see the table below.

S. No.	Agent ID	Name	City	State	State Code	City Code
1		Rahul Kumar	Kanpur	Uttar Pradesh	UP	0512
2		Sushil Raj	Kanpur	Uttar Pradesh	UP	0512
3		Arihant	Bareily	Uttar Pradesh	UP	0581
4		Sahil	Bareily	Uttar Pradesh	UP	0581

<tbody>
</tbody>

The State and city code are flowing from another sheet (using Index and Match) that i have not reproduced here.

So, i am trying to create a unique ID using the state and city code, like UP0512/01 for the first agent. i would want these to be created automatically since it is a large database. i was able to create the first part i.e. UP0512/ . how do i create the number part automatically?? i am stuck there.

Similarly, the ID for the next agent with the same city and state code should generate automatically like UP0512/02.

Any help would be greatly appreciated. Thanks a lot

 

[ganeshpoojary05]

Considering your State Code as Column F and City Code as column G. You can use the below formula.

=F2&G2&"/"&Row(A2)

 

[gallen]

You'd need a sequential number, stored somewhere that always gets 1 added to it once the previous value is assigned. I can't think of how to do this without VBA though. A search brought up this thread.

 

[swati kanodia]

Thanks a lot for the reply, but i am afraid this would not solve my problem as i would want to start the id from 1 again for a different city. Thanks again

 

[swati kanodia]

Actually i found another way of doing that using COUNTIF on the city name