Creating Unique Id for a dataset

[Aditya Jadhav]

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

[#2 · Accepted solution]

@Aditya Jadhav 

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.

[#3]

@Sergei Baklan 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?

[#4]

@Aditya Jadhav 

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.

[#5]

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.

[#6]

@Aditya Jadhav 

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.

[#7]

@Peter Bartholomew , 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.

[#8]

@Sergei Baklan 

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.