Excel 2010, generate 6 digit unique random number for ID column A

[fernandg]

How do I create a 6 digit unique random number for use as an ID in column A. Once created the rows with preexisting 6 digit unique random ID numbers must not change every time new rows are added.

Thank you,
Gary

 

[shg]

Why not just use sequential numbers: 1000000, 1000001, 1000002, ...?

 

[fernandg]

The 6 digit random numbers are used for student digital security door codes and so it would be easy for a student to take their door code number and just increment it and gain access using someone else s code.

 

[shg]

Enter =RANDBETWEEN(1000000,9999999) down a column.

That doesn't guarantee there are no duplicates, but someone would have to try a lot of doors to find two that open with the same code.

If that doesn't work, you could use a UDF:

Public Function RandLong(Optional iMin As Long = 1, _</SPAN>
                         Optional iMax As Long = -2147483647, _</SPAN>
                         Optional bVolatile As Boolean = False) As Variant</SPAN>
    ' UDF only!</SPAN>
 
    ' Returns numbers between iMin and iMax to the calling range</SPAN>
    ' UDF wrapper for aiRandLong</SPAN>
   
    ' shg 2008</SPAN>
 
    Dim nRow        As Long     ' rows in calling range</SPAN>
    Dim nCol        As Long     ' columns in calling range</SPAN>
    Dim iRow        As Long     ' row index</SPAN>
    Dim iCol        As Long     ' col index</SPAN>
    Dim aiTmp()     As Long     ' 1D temp array</SPAN>
    Dim aiOut()     As Long     ' output array</SPAN>
 
    If bVolatile Then Application.Volatile True</SPAN>
 
    With Application.Caller</SPAN>
        nRow = .Rows.Count</SPAN>
        nCol = .Columns.Count</SPAN>
    End With</SPAN>
 
    ReDim aiOut(1 To nRow, 1 To nCol)</SPAN>
    If iMin = 1 And iMax = -2147483647 Then iMax = nRow * nCol</SPAN>
    aiTmp = aiRandLong(iMin, iMax, nRow * nCol)</SPAN>
 
    For iRow = 1 To nRow</SPAN>
        For iCol = 1 To nCol</SPAN>
            aiOut(iRow, iCol) = aiTmp((iCol - 1) * nRow + iRow)</SPAN>
        Next iCol</SPAN>
    Next iRow</SPAN>
 
    RandLong = aiOut</SPAN>
End Function</SPAN>
 
Public Function aiRandLong(iMin As Long, _</SPAN>
                           iMax As Long, _</SPAN>
                           Optional ByVal n As Long = -1, _</SPAN>
                           Optional bVolatile As Boolean = False) As Long()</SPAN>
    ' shg 2008
    ' UDF or VBA</SPAN>
 
    ' Fisher-Yates shuffle</SPAN>
    ' Returns a 1-based array of n unique Longs between iMin and iMax inclusive</SPAN>
    Dim aiSrc()     As Long     ' array of numbers iMin to iMax</SPAN>
    Dim iSrc        As Long     ' index to aiSrc</SPAN>
    Dim iTop        As Long     ' decreasing upper bound for next selection</SPAN>
 
    Dim aiOut()     As Long     ' output array</SPAN>
    Dim iOut        As Long     ' index to aiOut</SPAN>
 
    If bVolatile Then Application.Volatile True</SPAN>
 
    If n < 0 Then n = iMax - iMin + 1</SPAN>
    If iMin > iMax Or n > (iMax - iMin + 1) Or n < 1 Then Exit Function</SPAN>
 
    ReDim aiSrc(iMin To iMax)</SPAN>
    ReDim aiOut(1 To n)</SPAN>
 
    ' init iSrc</SPAN>
    For iSrc = iMin To iMax</SPAN>
        aiSrc(iSrc) = iSrc</SPAN>
    Next iSrc</SPAN>
 
    iTop = iMax</SPAN>
    For iOut = 1 To n</SPAN>
        ' Pick a number in aiSrc between 1 and iTop, copy to output,</SPAN>
        ' replace with the number at iTop, decrement iTop</SPAN>
        iSrc = Int((iTop - iMin + 1) * Rnd) + iMin</SPAN>
        aiOut(iOut) = aiSrc(iSrc)</SPAN>
        aiSrc(iSrc) = aiSrc(iTop)</SPAN>
        iTop = iTop - 1</SPAN>
    Next iOut</SPAN>
 
    aiRandLong = aiOut</SPAN>
End Function</SPAN>

E.g. select A1:A1000 and array-enter =RandLong(1000000, 9999999)

 

[Haldoug]

If you want to not have duplicates, and not do a visual basic arrangement, you might try putting 100000 in a1, putting a1 + INT(rand()*17) in a2, copying a2 down the rest of of the column. Then in column B, rand()*10000, and sort by column b.

 

[Rick Rothstein]

If you want to not have duplicates, and not do a visual basic arrangement, you might try putting 100000 in a1, putting a1 + INT(rand()*17) in a2, copying a2 down the rest of of the column. Then in column B, rand()*10000, and sort by column b.

The OP said "Once created the rows with preexisting 6 digit unique random ID numbers must not change every time new rows are added"... your suggested formulas will change their displayed values everytime the worksheet they are on gets recalculated.

 

[Haldoug]

I just noticed, and Rick Rothstein has pointed out, that I forget to mention to copy and paste special with values the results before you sort. Put mind in gear before hands typing in motion.

 

[fernandg]

The UDF works almost perfectly. A problem occurred when I deleted a row. This caused all of column A1 (the student security door codes (the 6 digit unique random numbers)) to regenerated new numbers so I lost the the original numbers. So I'm using a work around of; copy A1 then paste as text. This seems to work.
Thanks!

 

[shg]

It sounds like you made a worse mistake than that. If you had array-entered the formula in A1:A1000, Excel wouldn't let you delete a row in the range of the array. It sounds like you instead Ctrl-Entered the fomula, which would be the same as using RANDBETWEEN.

In any case, copying the results and pasting as values once generated makes sense to keep the formula from recalculating.

 

[fernandg]

I didn't figure out what array-entered looks like, even after 20 minutes of google searches. Oh well...