Solved: random but unique running 4 digit pin

I have a work sheet that has 249 rows of data (user identification) (columns A thru F.

I would like to generate a random but unique 4 digit pin number for each row (result to be place in column G.

However there is a slight catch...

lets say I add 20 more users.. i want the pin numbers generated for them but not change the previous ones...

I figure it will ahve to be a UDF with a formula call so that it only triggers if a cell is changed.. (guessing here)...

Copying the data values to another sheet for 'storage' is an option, actually anything is an option right now.

Key things:
1. Once a pin number is generated it can not change for that user.
2. no two users can have the same pin number

Thanks in advance for any help with this

Here are a couple examples.
[vba]
Option Explicit

Sub AddPins()

Dim i As Long
Dim LastRow As Long
Dim Pin As Long

Const UpperBound = 9999
Const LowerBound = 0

LastRow = Range("A65536").End(xlUp).Row
Randomize
For i = 2 To LastRow
If Range("G" & i).Text = "" Then
Do
Pin = Int((UpperBound - LowerBound + 1) * Rnd + LowerBound)
If Application.WorksheetFunction.CountIf(Range("G:G"), Pin) = 0 Then
Range("G" & i).Value = Pin
Exit Do
End If
Loop
End If
Next i

End Sub

Sub AddPins2()

Dim i As Long
Dim j As Long
Dim LastRow As Long
Dim Pin As String

Const UpperBound = 7
Const LowerBound = 1
Const Digits = 4

LastRow = Range("A65536").End(xlUp).Row
Randomize
For i = 2 To LastRow
If Range("G" & i).Text = "" Then
Do
Pin = ""
For j = 1 To Digits
Pin = Pin & Int((UpperBound - LowerBound + 1) * Rnd + LowerBound)
Next j
If Application.WorksheetFunction.CountIf(Range("G:G"), Pin) = 0 Then
Range("G" & i).Value = Pin
Exit Do
End If
Loop
End If
Next i

End Sub
[/vba]

The first one will generate a number from 0 to 9999 and check if it is unique and if so place it in G. If there is already a PIN in G then it will not be changed.

The second one will do the same thing but give you more control over the numbers. In this case only 1,2,3,4,5,6,7 can be chosen for each of the four digits.

Quote:

---

Originally Posted by dvenn

I have a work sheet that has 249 rows of data (user identification) (columns A thru F.

I would like to generate a random but unique 4 digit pin number for each row (result to be place in column G.

However there is a slight catch...

lets say I add 20 more users.. i want the pin numbers generated for them but not change the previous ones...

I figure it will ahve to be a UDF with a formula call so that it only triggers if a cell is changed.. (guessing here)...

Copying the data values to another sheet for 'storage' is an option, actually anything is an option right now.

Key things:
1. Once a pin number is generated it can not change for that user.
2. no two users can have the same pin number

Thanks in advance for any help with this

---

A non-VBA way

First, ensure cell B1 is empty and goto Tools>Options and on the Calculation
tab check the Iteration checkbox to stop the Circular Reference message.

Next, type this formula into cell A1

=IF(($B$1="")+(AND(A1>=0,COUNTIF(A$1:A$1000,A1)=1)),A1,RANDBETWEEN(1000,999 9)

it should show a 0

Copy A1 down as far as you want.

Finally, put some value in B1, say an 'x', and all the random numbers will
be generated, and they won't change.

To add new numbers, just drag and copy the last cell

To force a re-calculation, clear cell B1, edit cell A1, don't change it,
just edit to reset to 0, copy A1 down, and re-input B1.

thanks you for your suggestions. I am looking for a method in VBA so I will be using DRJ's solution.

Both suggestions work

Glad to help :beerchug:

Take Care