Generating Random Door Access Codes

Ray has the task of assigning and tracking door access codes for his company. These are random five-digit codes that start with either 2 or 3. Ray wonders if there is a way in Excel to generate such random codes for an individual, without the code regenerating each time the worksheet is recalculated and without duplicating a code previously used.

There are actually a few different ways that you can approach this problem. Generating the codes is easy to do using the RANDBETWEEN function. For instance, you could use the following:

=RANDBETWEEN(20000,39999)

This will generate a random five-digit code that starts with either 2 or 3, just as Ray needs. There are two problems, however. First, the function doesn't return unique values, meaning it could return the same value on different invocations of RANDBETWEEN. The second problem is that you cannot check (and disqualify) what is returned based on a list of "used codes" you may have. Getting over these issues requires just a bit more work to figure out, and the solutions will require the use of macros.

First, let's look at a very easy macro-based solution. In this approach, I'm going to assume that you have four columns of information in a worksheet. In column A you have possible door codes, in order from 20000 to 39999. (Yes, that means you are using 20,000 rows for all those values—20,001 rows if you have a column heading in row 1.) In column B you will place a person's name, in column C the date the code was assigned, and in column D the date the code was taken out of service.

The key to this approach is that you can use a macro to select one of the door codes (in column A) at random, and then it can check if there is anything in columns B:D for that code. If there is, then it picks a different code at random and continues this process until it finds an unused code. When it does, it sticks today's date into column C and selects the cell in column B so that you can enter a name of who the code is for. Here's the macro:

Sub PickCode()
    Dim bFlag As Boolean
    Dim iRow As Integer
    Dim sRange As String
    Dim c As Range

    bFlag = False    ' Assume we have not found a match

    Randomize
    While Not bFlag
        iRow = Int(Rnd * 20000)
        ' If you have a header row, then add 2
        ' If you have no header row, only add 1
        iRow = iRow + 2
        sRange = "B" & iRow & ":D" & iRow
        
        bFlag = True    ' Assume this row will be empty
        For Each c In Range(sRange)
            If Not IsEmpty(c) Then bFlag = False    ' Not empty; keep looking
        Next c
    Wend

    ' We only exit the While loop when we've found an empty code row
    Cells(iRow, 3) = Now()
    ' Remove the following two lines if you already
    ' formatted columns C and D as dates
    Cells(iRow, 3).NumberFormat = "mm/dd/yyyy"
    Cells(iRow, 4).NumberFormat = "mm/dd/yyyy"
    Cells(iRow, 2).Select
End Sub

You can assign this macro to a shortcut key or to the Quick Access Toolbar, where you can access it very easily. Note that it doesn't create a random door access code, but it picks a row at random from which you can see the code you previously entered into column A. (It runs very, very quickly, as there are so many rows from which to choose.)

If you would rather generate a door code directly (so your list will be much shorter than the 20,000 rows the previous approach used), then a different macro is obviously required. In this approach I'll assume, again, that the same four columns are used.

Sub AddCode()
    Dim bFlag As Boolean
    Dim iCode As Integer
    Dim iTopRow As Integer
    Dim iLastRow As Integer
    Dim sRange As String
    Dim c As Range

    ' Find bounds of existing door codes
    ' If there is a header row, then start with row 2
    iTopRow = 2
    iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    sRange = "A" & iTopRow & ":A" & iLastRow
    
    bFlag = False    ' Assume we have not found a valid code
    
    Randomize
    While Not bFlag
        iCode = Int(Rnd * 20000) + 20000
        
        bFlag = True    ' Assume this code will be good
        For Each c In Range(sRange)
            If c.Value = iCode Then bFlag = False     'Code already in use
        Next c
    Wend

    ' We only exit the While loop when we've found an unused code
    ' Increment the last row
    iLastRow = iLastRow + 1
    Cells(iLastRow, 1) = iCode
    Cells(iLastRow, 3) = Now()
    ' Remove the following two lines if you already
    ' formatted columns C and D as dates
    Cells(iLastRow, 3).NumberFormat = "mm/dd/yyyy"
    Cells(iLastRow, 4).NumberFormat = "mm/dd/yyyy"
    Cells(iLastRow, 2).Select
End Sub

The macro determines which rows, based on column A, contain already-assigned door codes. The starting and ending rows are placed in the iTopRow and iLastRow variables, respectively. A possible random door code is generated and stored in the iCode variable, then each of the existing door codes is checked to see if it matches the value in iCode. If there is a match, the loop repeats until such point as an unused code is generated.

At that point, the code is placed into the first blank cell in column A, today's date is placed in column C, and the cell in column B is selected so that you can enter the person's name.

If, for some reason, you cannot use macros in your workbook, then there is a formulaic approach you can use, but it requires the use of two additional cells. In explaining this, I'll assume that the same four columns are in use as previously described, with column A containing the list of assigned door codes. My two additional cells will be F2 and F3. In F2, just generate a random door code in the range you want:

=RANDBETWEEN(20000,39999)

In cell F3 you place your formula to determine if this random door code has previously been used:

=IF(ISNA(MATCH(F2,A:A,0)),"Unused Code", "CODE IN USE; PRESS F9")

This uses the MATCH function to determine if the random code (F2) appears anywhere in column A. If it doesn't, then an #N/A error is generated which is converted to TRUE by the ISNA function. This means that the text "Unused Code" will appear as the result of the formula.

If the code does appear in column A, then MATCH returns the row in which the match occurs, so the text "CODE IN USE; PRESS F9" is displayed. When F9 is pressed, a new random door code is generated and the appropriate message displayed. When you find a door code you can use, just manually add it to column A and fill out the other columns, as desired.

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