Forum Discussion
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 numbers from start.
Eg, Have a village named ABCDE and it has 600 entries, want to create a id which contains the first four letters of the village name i.e, ABCD-01 upto the number of entries in that village then start with the next village similarly.
Attached the sample sheet for ready reference.
Regards,
Aditya
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.
7 Replies
Note: This applies to a version of Excel as yet unreleased.
= SCAN("",villageList, LAMBDA(prevID,currVillage, LET( prevVillage, LEFT(prevID,4), prevSeq, RIGHT(prevID,2), newID, IF( LEFT(currVillage,4)=prevVillage, prevVillage & TEXT(prevSeq+1,"-00"), LEFT(currVillage,4) & "-01"), newID ) ) )It generates a spilt range and is well-suited to long lists because it is not scanning the entire list to determine each sequence number. On the other hand, I don't think I would use this to sell the idea of Lambda functions! The best thing that can be said about it is that it can be reduced to
= SCAN("", villageList, villageIDλ)which may be a little more visually appealing.
PeterBartholomew1 , I think that's bit overcomplicated. Structured table, simple formula, Enter and we are here. I guess we don't need the code somewhere outside, we need it within the table with names and other data.
True, but I didn't know how it was going to pan out when I started. My judgement on finishing was that "I wouldn't use this solution to sell the idea of Lambda functions". In essence , the requirement is for a structured set of primary keys for a database table and creating the result as a single array adds nothing of value.
I did go on to embed an additional Lambda function in place of LEFT(xxx,4) that returned the first 3 non-vowels, having used REDUCE to step through the vowels.
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.
