Numbering Formats
Before we get into a discussion on how to build a number, and indeed, before you implement the solutions I am about to discuss, you need to decide the format of the numbering you want to use.
Back in my warehouse and production control days, I quickly learned to dislike part numbers with meaning, they more often than not lead to misalignment, confusion and issues around running out of numbers in a range. For example, in a small goods business I worked with, the products had an six digit numbering system. The first two digits indicated the category the product sold into in the market, the next two digits sub-grouped the products and the remaining digits were unique identifiers for each product in the category. There is no doubt that for quick review and reporting purposes, the first two digits were brilliant, but two issues always created problems. The first is that some products would live at the boundaries between two categories and the market would occasionally shift the products at their end, so we would have to adjust our reporting model using exceptions.
The other issue was that products would be allocated numbers in the R&D stage and some products had many variants developed, but never produced. As result, some groups of products would actually run out of numbers in their range and the team would “steal” a number from another range.
I’m sure many of you have seen a mess like this. Companies like SAP addressed this issue in their ERP solutions by providing a lot of fields that allow the capture of meta data for product categorisation. They then allocate a ten digit “meaningless” number to identify the product. This is great, though data entry keystrokes are significantly increased and you often get sick of entering a lot of zeros. Further to that, it isn’t as easy to memorize a bunch of ten digit numbers. Humourously, it is still common for users to build meaning into the numbers by setting a fixed value for the first 4 to 6 digits, so you may as well allow alpha characters to make the number more memorable.
Personally, when it comes to ID’s for contracts and processes, I like meaning in the number and I like the numbers to be easily memorised so that I use them in conversations with the client to really embed the relationship between the documents, actions, records and the process. I encourage clients to include the ID in every email they send me so that a quick scan of my inbox rapidly brings me up to date with the processes I am managing.
So the model I will demonstrate now is made up of an alpha prefix followed by a two digit year identifier and a three digit unique identifier, for example, “PME15-123”. The prefix can be used to group processes any way you like. It could separate categories, business units or to separate process from contract. One interesting approach I have seen used three characters to identify the division of the parent company and then a further two digits to identify if the procurement was conducted by the business without procurement team support, so a process conducted with procurement would be “ICT15-123” whereas a process conducted without procurement support would be ICTBU15-123. Their procurement team had visibility of Purchase Order request and recorded the details, by flagging the processes in this way, management could immediately spot any anomalies of high value procurements with a “BU” number that should be further reviewed.
Further strengths of this model are that each prefix has its own register of numbers and they are all reset to zero when the year is rolled over, so unless you have more than 999 processes in a year, you won’t run out of numbers. If you do, you could add another digit, but you also shouldn’t be considering Excel as your management tool if your volumes are that high.
Setting up the registers
Ok, so lets start building this thing. The first step is to establish our list of prefixes we want to use. For this demo I’ll use the last model I discussed with three letters that identify my company and a further two digit suffix to the prefix of “BU” to identify if the procurement team were not consulted in the purchase.
So on our Lookups sheet we will create a new column called something imaginative like “Prefixes”. In that column list your prefixes you want to use and then we will assign a Named 
Range to the list using the dynamic named range approach. That way we can add more prefixes any time we choose. This list will be used in selectors for the users to identify the prefix they want to apply to a new record.
Next we need to the right of that another column called “Register”. In this column we place the unique identifier number, just enter zero in the cells that align with each prefix you have entered. Don’t worry about formatting the number to have leading zeros, we’ll take care of that in our code later.
Next we want another column called “CurrentYear” and we’ll enter, for me at present, “15” for 2015. Now we could generate this number in the code as we go with something like Format(Now(), “yy”), but I prefer to give the users control over when the year rolls over so that the unique identifiers are zeroed at the same time and it also allows the users to determine if the year will roll on a calendar year or a financial year.
So now we have our components ready for assembly.
User interface for generating ID numbers
If you are only going to use one or two prefixes, you could create a button for each option and save your users a couple of clicks, however, for greater flexibility in the number of prefix option available and the ability to change and existing prefix, a simple user form is the way to go. This way we can use a combobox to allow the user to select the prefix they want to apply to a new ID.
Populating the dropdown list of the combobox is a relatively straight forward loop statement to step through our list of available prefixes:
Private Sub UserForm_Initialize()
Dim c As Range
For Each c In Range("LU_Prefix")
Me.cboPrefix.AddItem strPrefix
Next c
End Sub
Another consideration is situations where you may have legacy numbers on contracts that you discover when there is an issue or they are about to expire, or ID’s generated by other areas of the business or systems that get handed to your team halfway through. To accommodate this I include a free text textbox and we will look at how we manage this later.
Pulling it together
Alright, so we have all the building components for the ID and our user has selected the applicable prefix. How do we paste this all together?
This is not at all difficult, the basic concept is that we need a variable for each component, a method of getting the values for each variable, string concatenation to glue it together and then to write it to the input form.
Private Sub cmdCreateID_Click()
Dim strPrefix As String
Dim strSeparator As String
Dim strNumber As String
Dim strID As String
Dim strYear As String
Dim intPCol As Integer
Dim intNumber As Integer
Dim lstRegister As ListObject
Dim wsLU As Worksheet
Dim wsRM As Worksheet
Set lstRegister = Sheets("Register").ListObjects("tblRegister")
Set wsLU = Sheets("Lookups")
Set wsRM = Sheets("Record Maintenance")
strSeparator = "-"
strPrefix = Me.cboPrefix.Value
'first check if an id has been entered
If Me.txtID.Value <> "" Then
'if it has then get the value and check if it is used already
strID = Me.txtID.Value
Set cell = lstRegister.DataBodyRange.Find(strID)
'if we find a match there is a duplicate
If Not cell Is Nothing Then
MsgBox strID & " is already in use in the register. " & vbCrLf & vbCrLf & _
"Either enter a new ID or search for the existing record.", _
vbOKOnly, "Duplicate Found"
Exit Sub
End If
Else
'if there isn't an ID entered, lets create one
'first get the next number
'get the prefix column
intPCol = WorksheetFunction.Match("Prefix", wsLU.Range("1:1"), 0)
'find the prefix
intRow = 4
While wsLU.Cells(intRow, intPCol).Value <> strPrefix
intRow = intRow + 1
Wend
'then get the next number
intNumber = wsLU.Cells(intRow, intPCol + 1).Value + 1
strNumber = CStr(Format(intNumber, "000"))
wsLU.Cells(intRow, intPCol + 1).Value = intNumber
'next get the year value
strYear = CStr(wsLU.Range("LU_Current_Year").Value)
'now build the ID
strID = strPrefix & strYear & strSeparator & strNumber
End If
'write the ID to the maintenance sheet
wsRM.Range("RM_Number").Value = strID
'close the form to show all is good
Unload Me
End Sub
Allow for manual ID entry
Remember that I mentioned that I added a textbox to the form to allow the user to enter an ID of their choosing, this ID must still be unique, so the first thing we do is check if there is a value in this field and then check if the value already exists in our register:
'first check if an id has been entered
If Me.txtID.Value <> "" Then
'if it has then get the value and check if it is used already
strID = Me.txtID.Value
Set cell = lstRegister.DataBodyRange.Find(strID)
'if we find a match there is a duplicate
If Not cell Is Nothing Then
MsgBox strID & " is already in use in the register. " & vbCrLf & vbCrLf & _
"Either enter a new ID or search for the existing record.", _
vbOKOnly, "Duplicate Found"
Exit Sub
End If
Else
Checking for a value is easy with ‘If Me.txtID.Value <> “” Then’, if we do have a value, we need to check if it already exists in the Register. Because I have defined my Register as a Table, this can be done by using the ‘Set cell = lstRegister.DataBodyRange.Find(strID)’, which finds a given value anywhere in a Table. Next we test if anything was found with ‘If Not cell Is Nothing Then’. Where something was found, we have a duplicate, so we need to warn the user and stop the process.
If the test didn’t find a duplicate, we simply carry on as we have assigned the manual value to the variable of strID and this is the value written to the Record Maintenance sheet later on.
Generate an ID
If the user has not entered a manual ID, then we need to build one for them. We get our prefix from the combobox with ‘strPrefix = me.cboPrefix.value’, too easy. The year component is pretty straight forward, particularly if we remembered to apply a named range to the field, then we can use ‘strYear = CStr(wsLU.Range(“LU_Current_Year”).Value)’. Note that I use the CStr to cast the value to a string, this may not be necessary, but avoids any potential issues if someone later formats the year field to something other than General or Text.
Now for the slightly trickier bit, getting the unique number associated with the prefix. There are a number of different ways to do this, but as I have mentioned before in this series, I am a big fan of the process of looping through cells so I can see exactly what is going on. Because we built our combobox list from the list we are about to search, it should be impossible for there not to be a match, so we will simply search for the value using the cells stepping method without an error testing:
'get the prefix column
intPCol = WorksheetFunction.Match("Prefix", wsLU.Range("1:1"), 0)
'find the prefix
intRow = 4
While wsLU.Cells(intRow, intPCol).Value <> strPrefix
intRow = intRow + 1
Wend
'then get the next number
intNumber = wsLU.Cells(intRow, intPCol + 1).Value + 1
strNumber = CStr(Format(intNumber, "000"))
wsLU.Cells(intRow, intPCol + 1).Value = intNumber
So that we can protect the code against someone later inserting a new column in the Lookups sheet before the Prefix Column, I need to find the column number for the Prefix column. This is a handy feature of Excel VBA, you can use the standard worksheet functions and in this case I have used the Match function with ‘intPCol = WorksheetFunction.Match(“Prefix”, wsLU.Range(“1:1”), 0)’. This is searching in my first row for the word “Prefix” and returns the column number.
Now I have the column number, I can combine that with the intRow variable I defined and step through the prefixes until I find the one the user has selected in the combobox on the form with a while statement. This simply steps down through the cells in the column until it finds a match for the value in strPrefix, once found, intRow is now set to the value of the row the selected prefix.
So now we have our column and row, we can get the value, I do this with three steps; 1) Get the current value, increment by 1 and save it to the intNumber variable; 2) reformat the number into a three character string with zero padding, i.e. ‘001’, and save that to the strNumber variable; 3) then write the intNumber back to the prefix register location on the Lookups sheet so that we have incremented that value for the next use.
Final Assembly
We now have all our building blocks:
- strPrefix = “PME”
- strYear = “14”
- strSeparator = “-“
- strNumber = “003”
Building the ID just requires a concatenation of these values with ‘strID = strPrefix & strYear & strSeparator & strNumber’ resulting in a unique ID of “PME14-003”.
Outside of the IF statement I have a single line to write the value of strID to the Number field of the Record Maintenance form, ‘wsRM.Range(“RM_Number”).Value = strID’. This will write the value of strID whether it was populated by the first manual number steps or through the auto generation of a unique ID. Then the final step is to unload the ID Creation form with the wonderfully simple ‘Unload Me’ statement.
So it is a simple as that. I’m sure you can see the flexibility this method provides for allowing the end users to define as many prefixes as they choose. You may also be thinking of ways you can modify this model to allow users to have even greater flexibility in defining the format and structure of the IDs. You can also use a very similar model to generate unique primary keys for an Access database table and I have done so in my own solutions.
I hope you find this useful in your next project and as always, please leave a comment or drop me a line to let me know how if you have any further questions or even how you have used this approach to solve your own challenges.