Forum Discussion
Solved
Creating Unique Id for a dataset
Hi, Have a list of villages and want to create a Id for each village stating the number for that village. Want to give numbers which go on increasing till the village changes and again start to give...
For that formula could be
=LEFT(A2,4) & "-" & COUNTIF($A$2:A2,A2)select the range and Ctrl+D it down.
However, first 4 letter could be the same for different locations, perhaps more letter to take for ID.
SergeiBaklan One last thing in this thread is can a code be created on specific letters like for eg, if the village name is Banpuri and want to give a code like BNP how can I do that?
You need to inform Excel somehow that Banpuri takes BNP code. That could be separate table somewhere in workbook which maps location names on their codes. Perhaps such table exists on the web, you only need to copy/paste it in Excel.
In addition, I'd strongly recommend to transform ranges to structured table Overview of Excel tables (microsoft.com) , that makes life much easier.
Finally, if you have something like this
formula for the code could be
= IFNA( INDEX( Codes[Code], MATCH( [@Village], Codes[Village], 0 ) ), LEFT( [@Village], 4) ) & "-" & COUNTIF( $A$2:A2, [@Village] )If code is found in supporting table, we take it, otherwise first 4 letters.