...

Generate Codes Dynamically with a Pattern from Your Data

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.

  1. Combine the contents of the Name and State columns using a TEXTJOIN function and a space as delimiter. Remember the table is named “Data”

  2. Next, I want to split the text at each space so each word appears in a separate column, using TEXTSPLIT function
  3. Extract the first character of each word using a LEFT function
  4. Combine the Extracted character together, using another TEXTJOIN function
  5. 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:

  6. Copy the previous function to the Office Clipboard (without the = sign) for later use.
  7. 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.
  8. The result must appear as 4 digits. So I use a TEXT function to add leading zeros.
  9. Copy the previous function to the Office Clipboard (without the = sign) for later use.
  10. The final result requires combining the functions in step 5 & 8 with a “-“ in between. We will do that in the final function.
  11. 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

Share This Post
Have your say!
0 0

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Subscribe to our Newsletter

Receive 7 Instant Gifts when you Subscribe to my monthly Newsletter below.

Thanks for Downloading Your Gift

We have sent your quick reference guide for creating Dashboard.

Please check your inbox to receive Your Gift.

Happy Learning !!

Subscribe to our Newsletter

Receive 7 Instant Gifts when you Subscribe to my monthly Newsletter below.

Seraphinite AcceleratorOptimized by Seraphinite Accelerator
Turns on site high speed to be attractive for people and search engines.