Sometimes you need to create a unique code for a list of Products, Sales transactions, Students or Patients records or any other work situation. Having a unique identifier for each record is very important in Excel, In Access, in Data Modeling and in Power BI.
In this tutorial, I teach you how to create a Unique Alphanumeric code with a predictable pattern that takes its structure from your own data, while being sequential without any duplicates. Adding more records to the source results in automatically generating a new code.
I’m using Microsoft 365 Dynamic Array Functions
So, let’s see how I build the logic step by step in Excel.
The Work Situation
We have a table named “Data” in columns A:B of the worksheet. I column Awe have a full name while in column B we have a State.
You can download the Exercise File and follow along by clicking on the link
The goal is to generate an Alphanumeric code for each record that uniquely identifies each record. The code will include the first character of each word in columns A & B then a sequential number of 4 digits separated by a dash from the characters, like this screenshot:
The Functions I will use
- I am using for this project Microsoft 365 with Dynamic Array Functions, which have the following advantage:
- You create the function only once
- The function Spills to the adjacent cells.
- No worries about locking cell references
- Using a table column or name as input values makes them expandable, when more data is added to the source.
- We can create Variable and assign values/ Calculations to them. Then use these variables within the function.
- Some of the functions have a recursive behavior.
The LET Function
The LET function assigns names to calculation results. This allows storing intermediate calculations, values, or defining names inside a formula. These names only apply within the scope of the LET function. Similar to variables in programming, LET is accomplished through Excel’s native formula syntax.
To use the LET function in Excel, you define pairs of names and associated values, and a calculation that uses them all. You must define at least one name/value pair (a variable), and LET supports up to 126.
SEQUENCE Function
The SEQUENCE function allows you to generate a list of sequential numbers in an array, such as 1, 2, 3, 4.
=SEQUENCE(rows,columns,start,step)
TEXTJOIN Function
The TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges
=TEXTJOIN(delimiter,ignore_empty,text1,…)
LEFT Function
LEFT returns the first character or characters in a text string, based on the number of characters you specify.
=LEFT(text,num_chars)
COUNTA Function
The COUNTA function counts the number of cells that are not empty in a range
=COUNTA(value1,value2,…)
TEXT Function
The TEXT function lets you change the way a number appears by applying formatting to it with format codes. It’s useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols
=TEXT(Value you want to format, “Format code you want to apply”)
BYROW Function
Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
=BYROW(array, Lambda function(row))
LAMBDA Function
Use a LAMBDA function to create custom, reusable functions and call them by a friendly name. The new function is available throughout the workbook and called like native Excel functions.
You can create a function for a commonly used formula, eliminate the need to copy and paste this formula (which can be error-prone), and effectively add your own functions to the native Excel function library. Furthermore, a LAMBDA function doesn’t require VBA, macros or JavaScript, so non-programmers can also benefit from its use.
=LAMBDA([parameter1, parameter2, …,] calculation)
HSTACK Function
Appends arrays horizontally and in sequence to return a larger array.
=HSTACK(array1,[array2],…)
The Concept
To simplify the concept, I will build 2 functions in Columns F and G, each one with multiple levels of nesting. Then, I will combine the previous 2 functions in a bigger final function.
You can download the Exercise File and follow along by clicking on the link
Note: using the office clipboard to copy subsets of a function is an extremely useful practice.
- Combine the contents of the Name and State columns using a TEXTJOIN function and a space as delimiter. Remember the table is named “Data”
- Next, I want to split the text at each space so each word appears in a separate column, using TEXTSPLIT function
- Extract the first character of each word using a LEFT function
- Combine the Extracted character together, using another TEXTJOIN function
- We need to repeat the same set of actions for each row in the Data Table. So I use a BYROW function to do that. The BYROW requires an array, which is the source table and a LAMBDA function that will have a parameter “x” representing each row in the array on which we will apply the set of functions. Note that I replaced Data[@] by the variable “x”
This function spills to adjacent cells and the result looks like this: - Copy the previous function to the Office Clipboard (without the = sign) for later use.
- In column G, we need to create a sequence of numbers which starts at 1 and increments by 1 for each record, up to the last record. How many records we have? Use a COUNTA function to count the Name column.
- The result must appear as 4 digits. So I use a TEXT function to add leading zeros.
- Copy the previous function to the Office Clipboard (without the = sign) for later use.
- The final result requires combining the functions in step 5 & 8 with a “-“ in between. We will do that in the final function.
- Delete all the previous steps to start creating the final function in column D.
Let’s put it all in one function
In cell D2 I start creating a LET function. I will create 6 variables and I name them A, B, C, D, E, F
- The variable A ►Stores the list of Names
- The variable B ►Counts how many names there are.
- The variable C ►Stores the entire source table
- The variable D ►Creates the list of sequential numbers (paste from Clipboard)
- The variable E ►Stores the characters abbreviation (paste from Clipboard)
- The variable F ►Stacks characters and digits
I expand the Formula Bar to write my function in multiple lines and improve the legibility of the function. The shortcut to Expand/Collapse is CTRL + SHIFT + U.
To move to a new line we use the shortcut ALT + Enter.
Each line is followed by a comma, except the last line (the calculation).
Substitute arguments in B & E by the corresponding Variables.
Create another variable F which puts the Characters (E) and numbers (D) side by side.
Now in the last line of the LET we create the calculation that will look at the array “F” created in memory and for each row will join E and D with a “-“ in between.
Hit Enter … Voila
We get the desired result!
We need to test
Below the “Data” table, we have more records. Cut and paste these records in the first empty row below the table. The function expands to return the codes for the new records as well.
And we finished creating the project.
You can Watch the Video Tutorial on YouTube by Clicking here below
