Generating a Unique ID Number

Amy wants to create a unique ID for a membership database she is currently storing in Excel. She wonders if there is a formula that can do this easily.

The short answer is "yes, you can do this." But it is not a simple matter of somehow saying to Excel, "give me a unique identifier." The program has no way of knowing what that means; it is something that you must define.

For instance, the simplest unique identifier you can come up with is a series of sequential numbers. These are, by definition, unique. They may not, however, fit your needs. You may want, instead, ID numbers that are not sequential. Or you may want them to contain digits and letters. Or you may want them to consist of just letters. Or you may want them to always be a certain length. Or you may want the ID number to be based on something related to the member. Or... well, you get the idea: You must define what you want a "unique ID number" to look like. Once you know this, you can utilize Excel to come up with a number that fits the bill.

For example, let's say that you want the ID number to be just digits, but not sequential. You also want it to be six digits in length. You could easily accomplish that by putting the following into cell A1:

=TEXT(RANDBETWEEN(1,999999),"000000")

Now, copy the formula down as many cells as you desire. The chances of you generating non-unique values is pretty slim, but the ID numbers will regenerate (because of the RANDBETWEEN function) each time the worksheet recalculates. So, at some point, you'll want to make the ID numbers static by selecting the range of cells, pressing Ctrl+C, and then using Paste Special to paste values in place of the original formulas.

To absolutely ensure you have no duplicates in the list, you can use the Remove Duplicates tool from the Data tab of the ribbon. Or, if you are using Excel 2021 or later or Excel in Microsoft 365, you can ensure you have no duplicates by using the following formula in cell B1:

=UNIQUE(A1:A250)

Just make sure that you use the range of cells in column A that contain your ID numbers. The result, in column B, is however many IDs you need, verified to be unique and fitting your defined needs for the IDs.

If you want to modify what your ID numbers include, then all you need to do is go back to the beginning of this described process and modify the formula you use in cell A1. For instance, you may want something super-simple that consists of just the letters "MN" (for "member number") followed by a sequential five-digit number. All you need to do is type this into cell A1:

MN00001

Now, select that cell and drag the fill handle downward. You now have all your membership numbers.

Or, lets say you want the IDs to consist of three uppercase letters, followed by a dash, followed by four digits, then you could use the following in cell A1:

=CHAR(RANDBETWEEN(65, 90)) & CHAR(RANDBETWEEN(65, 90)) & CHAR(RANDBETWEEN(65, 90)) & "-" & TEXT(RANDBETWEEN(1,9999),"0000")

Copy it downward and use the same process described earlier to make the values static and to remove any potential duplicates.

If you want to get super-secure with the ID number you come up with, there are ways to do so. (Granted, this is like shooting a mosquito with a cannon—a bit of overkill—but it may be necessary for some users.) Here is a macro that allows you to create a hashed string for whatever "seed" information you pass to it:

Public Function HashString(Text As String, T As Integer) As String
    Dim Encode As Object
    Dim Crypto As Object
    Dim Provider(3) As String
    Dim Hash() As Byte
    Dim n As Integer
    Dim Response As String

    Const Security = "System.Security.Cryptography"
    Provider(1) = ".MD5CryptoServiceProvider"    '16 bytes, 32 chars
    Provider(2) = ".SHA1CryptoServiceProvider"   '20 bytes, 40 chars
    Provider(3) = ".SHA256Managed"               '32 bytes, 64 chars

    If (T > 0) And (T < 4) Then
        Set Encode = CreateObject("System.Text.UTF8Encoding")
        Set Crypto = CreateObject(Security & Provider(T))
        Hash = Crypto.ComputeHash_2(Encode.GetBytes_4(Text))
        For n = LBound(Hash) To UBound(Hash)
            Response = Response & Hex(Hash(n) \ 16) & Hex(Hash(n) Mod 16)
        Next
    Else
        Response = "INVALID TYPE"
    End If
    HashString = Response
End Function

To use it, you would use something like this in a cell:

=HashString(A1 & B1, 1)

In this usage, I'm assuming that the person's name is in A1 and their address is in B1. This comprises the first parameter, which is the "seed" of which I spoke. As long as the seed is unique (regardless of the length), then the hash that is generated will be both unique and secure. The second parameter is either 1, 2, or 3, depending on the type of algorithm you want to use to generate the hash. (The code for the macro shows the values stored in the Provider() array so that you can see the algorithm.) Depending on whether you use 1, 2, or 3, the routine will return 32, 40, or 64 characters that can be used as your unique ID number.

If the code doesn't work for you, then you'll need to configure Windows so that the proper developer's framework is available to Excel. (The System.Text.UTF8Encoding object reliest on the .NET framework from Microsoft.) To enable it, follow these steps:

1. Get out of Excel (and, for good measure, all other applications)
2. Press the Windows key and type "windows features." Windows shows you some options.
3. Select Turn Windows Features On and Off from the search results.
4. Click the check box for .NET Framework 3.5.
5. Click OK.

At this point Windows installs the .NET Framework. If necessary, it will download it to your system in order to install it. When installation is complete, you may be prompted to restart your system. When you get back into Excel, the macro should work properly.

Overkill, right? Well, you can go one better and generate a GUID, which is a much longer ID number that is vanishingly unlikely to be duplicated—for all intents and purposes, a random, unique ID number. This would be particularly helpful if you are generating IDs for use in a large organization across multiple locations worldwide. (I say this knowing, full well, that operations of this size would be unlikely to keep track of the IDs in Excel, opting, instead, for dedicated database applications.) You can find info on how to generate GUIDs within Excel by checking out this article from Chip Pearson's site:

http://www.cpearson.com/Excel/CreateGUID.aspx

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13921) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.