Generating Random Door Access Codes

Written by Allen Wyatt (last updated November 26, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


2

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Weird Mouse Shortcut

If you like to use the mouse in your worksheet navigation efforts, you'll want to pay attention to this tip. Here you ...

Discover More

Automatically Using Smart Quotes

As a way to make your documents look more professional, Word can utilize "smart quotes" for both quote marks and ...

Discover More

Setting Defaults in the Cross-reference Dialog Box

Some types of documents rely on cross-references quite a bit. Setting up the Cross-reference dialog box the first time in ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Filtering to a Standard Deviation

When you are working with large data sets, you may want to filter the information in those data sets according to various ...

Discover More

Transposing and Linking

Sometimes it is helpful to look at data that is rotated 90 degrees. Excel allows you to use Paste Special to transpose ...

Discover More

Counting Cells Containing a Formula

Cells in a worksheet can contain different types of information, such as numbers, text, and formulas. If you want to ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is seven more than 9?

2022-11-26 10:53:53

Tomek

Another trick you can use is to start typing in a formula =F2 in the destination cell, but not pressing the ENTER key. Instead press F9. This will result in the evaluation of the partially entered formula to the value in cell F2, and when you press ENTER that explicit number will be entered in your destination cell. Again, the number in F2 will be re-generated, but like described in my previous comment, the number placed in the destination cell will be the one that had been evaluated already.


2022-11-26 10:52:08

Tomek

Instead of manually transcribing the number from the cell F2, you can copy the content of F2 then paste-values only into the destination cell. This will cause recalculation of the sheet and generation of a new random number in the cell F2, but what will be pasted is the number in the cell F2 before recalculation, hence a number that has been already evaluated by the formula in the cell F3.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.