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.

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