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

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.

**Program Successfully in Excel!** John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out *Excel 2013 Power Programming with VBA* today!

If you want to figure out the sum of all cells that contain formulas, there are a couple of ways you can go about it. ...

Discover MoreIf you have a series of values in a range of cells, you may wonder how many of those values are even and how many are ...

Discover MoreIf you keep phone numbers in an Excel worksheet, you may need a way to add an area code to the beginning of all of the ...

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

2022-11-26 10:53:53

Tomek

2022-11-26 10:52:08

Tomek

**This site is for you!** If you
use an earlier version of Excel, visit
our *ExcelTips* site focusing on the menu interface.

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

Copyright © 2024 Sharon Parq Associates, Inc.

## Comments