Forum Discussion

Aditya Jadhav's avatar
Aditya Jadhav
Brass Contributor
Nov 20, 2021
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...
excel
Formulas and Functions
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    Nov 20, 2021

    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.

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Nov 20, 2021

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.

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 20, 2021

    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.

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Nov 20, 2021

      SergeiBaklan 

      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.

Resources