Forum Discussion
Creating Unique Id for a dataset
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.
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.
- With regard to your last query, a lookup table might be the best solution.
It would be possible if the rule were to omit vowels, but even that is not a trivial task.
