Non updatable Unique Random Number

I work in a hospital and I have a worksheet of data from the patients we
treat. One column has their ID number and I want to add a column next to this
to which I can add a series of random numbers as a security measure when
passing data to different departments. I have read a number of threads on
this site and feel comfortable in generating the unique random numbers
(thanks to Bernd Plumoff's UDF). But what I can't seem to manage is to keep
this column of unique random numbers from updating, which defeats the
purpose. I realise I can just use RAND() and then hit F9 to turn this into a
random number but I need to be sure sure that this rather tedious method
(when doing it for 2000 records) will not produce duplicate records.

I need to be able to generate a column of randomly assigned, unique
integers, in a number range that I can specify and that are not updated once
they have been generated.

Thank you for any suggestions,
Ian.

Here is one technique

First, ensure cell A1 is empty and goto ToolsOptions and on the Calculation
tab check the Iteration checkbox to stop the Circular Reference message.

Next, type this formula into cell B1
=IF(($A$1="")+(AND(B10,COUNTIF($B$1:$B$2000,B1)=1 )),B1,INT(RAND()*2000+1))
it should show a 0

Copy B1 down to B2000.

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

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


Obviously, A1 can be any cell that you want.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ian" wrote in message
...
I work in a hospital and I have a worksheet of data from the patients we
treat. One column has their ID number and I want to add a column next to
this
to which I can add a series of random numbers as a security measure when
passing data to different departments. I have read a number of threads on
this site and feel comfortable in generating the unique random numbers
(thanks to Bernd Plumoff's UDF). But what I can't seem to manage is to
keep
this column of unique random numbers from updating, which defeats the
purpose. I realise I can just use RAND() and then hit F9 to turn this into
a
random number but I need to be sure sure that this rather tedious method
(when doing it for 2000 records) will not produce duplicate records.

I need to be able to generate a column of randomly assigned, unique
integers, in a number range that I can specify and that are not updated
once
they have been generated.

Thank you for any suggestions,
Ian.

"Ian" skrev i en meddelelse
...
I work in a hospital and I have a worksheet of data from the patients we
treat. One column has their ID number and I want to add a column next to
this
to which I can add a series of random numbers as a security measure when
passing data to different departments. I have read a number of threads on
this site and feel comfortable in generating the unique random numbers
(thanks to Bernd Plumoff's UDF). But what I can't seem to manage is to
keep
this column of unique random numbers from updating, which defeats the
purpose. I realise I can just use RAND() and then hit F9 to turn this into
a
random number but I need to be sure sure that this rather tedious method
(when doing it for 2000 records) will not produce duplicate records.

I need to be able to generate a column of randomly assigned, unique
integers, in a number range that I can specify and that are not updated
once
they have been generated.

Thank you for any suggestions,
Ian.

Hi Ian

Here's a VBA solution, which inserts a random number, when you doubleclick
a cell in one of the defined ranges.

In the sheet:

1. Rightclick the sheet tab and choose "View code" (or similar)
2. Copy the code "Private Sub Worksheet_BeforeDoubleClick" below
3. Paste it to the righthand window.

The code defines a random number of ranges on this sheet, with their own
pools of random numbers.
E.g. Array("B2:B2000", 100, 10000) defines the range "B2:B2000" with random
numbers 100 through 10000. You can add your own ranges using the set up
shown below.
For one range: RandData = Array(Array("B2:B2000", 100, 10000))


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
'Leo Heuser, 18 Sep. 2006
Dim Answer As Variant
Dim CheckRange As Range
Dim Counter As Long
Dim RandData As Variant
Dim FirstNum As Long
Dim LastNum As Long

' RandData: Array(Range, FirstNumber, LastNumber)
' Range must be in this sheet.

RandData = Array(Array("B2:B2000", 100, 10000), _
Array("F15:F23", 3, 11), _
Array("G1:H6", 2, 13))

For Counter = LBound(RandData) To UBound(RandData)
Set CheckRange = Range(RandData(Counter)(LBound(RandData)))
If Not Intersect(Target, CheckRange) Is Nothing Then
If Target.Cells.Count 1 Then Exit Sub
Cancel = True

If Not (IsEmpty(Target)) Then
Answer = MsgBox("Do you want a new random number?", _
vbDefaultButton2 + vbYesNo)
If Answer vbYes Then Exit Sub
End If

Target.Value = NewRandNum(CheckRange, _
RandData(Counter)(LBound(RandData) + 1), _
RandData(Counter)(LBound(RandData) + 2))
End If
Next Counter
End Sub



4. Choose Insert Module
5. Copy the code below and paste it to the righthand window


Function NewRandNum(Randrange As Range, FirstNum As Variant, _
LastNum As Variant) As Long
'Leo Heuser, 18 Sep. 2006
'When a number is inserted in a cell, it's not updated ever,
'and it is removed from the random number pool for that range.
Dim Counter As Long
Dim Counter1 As Long
Dim RandCol As New Collection
Dim RandRangeValue As Variant

Randomize

RandRangeValue = Randrange.Value

On Error Resume Next

For Counter = FirstNum To LastNum
RandCol.Add Item:=Counter, key:=CStr(Counter)
Next Counter

For Counter = 1 To UBound(RandRangeValue, 1)
For Counter1 = 1 To UBound(RandRangeValue, 2)
If Not (IsEmpty(RandRangeValue(Counter, Counter1))) Then
RandCol.Add Item:=RandRangeValue(Counter, Counter1), _
key:=CStr(RandRangeValue(Counter, Counter1))
If Err.Number 0 Then
RandCol.Remove CStr(RandRangeValue(Counter, Counter1))
Err.Number = 0
End If
End If
Next Counter1
Next Counter

RandNum = Int(Rnd() * RandCol.Count) + 1

NewRandNum = RandCol(RandNum)

On Error GoTo 0

End Function


6. Return to the sheet with AltF11 and save the workbook.

Ready to go :-)


--
Best regards
Leo Heuser

Followup to newsgroup only please.

What is the relationship between the number of records
and the number of iterations I have to do?
I tried 400 records and, in the formula, changed 2000 to 400.
I had to iterate 625 times before the numbers became unique.

I am afraid that I don't know, it has always been trial and error with me.
Perhaps someone else does know.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Herbert Seidenberg" wrote in message
oups.com...
What is the relationship between the number of records
and the number of iterations I have to do?
I tried 400 records and, in the formula, changed 2000 to 400.
I had to iterate 625 times before the numbers became unique.

"Leo Heuser" wrote:
.. You can add your own ranges using the set up shown below.
For one range: RandData = Array(Array("B2:B2000", 100, 10000))

Great sub, Leo !

Is there a simple way to bring over to Excel screen via say, an inputbox, so
that we can define the clickable ranges and the numeric limits below in Excel
itself ?

RandData = Array(Array("B2:B2000", 100, 10000), _
Array("F15:F23", 3, 11), _
Array("G1:H6", 2, 13))

Thanks
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Max,

Set rng = Application.Inputbox("Select range", Type:=8)

allows the selection of a range mid-macro, and a couple more inputboxes
could be added to get the limits, but that sounds like it would be better to
throw up a simple form to get the details, easier to control.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Max" wrote in message
...
"Leo Heuser" wrote:
.. You can add your own ranges using the set up shown below.
For one range: RandData = Array(Array("B2:B2000", 100, 10000))

Great sub, Leo !

Is there a simple way to bring over to Excel screen via say, an inputbox,
so
that we can define the clickable ranges and the numeric limits below in
Excel
itself ?

RandData = Array(Array("B2:B2000", 100, 10000), _
Array("F15:F23", 3, 11), _
Array("G1:H6", 2, 13))

Thanks
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Bob, thanks. Any chance of a sample to work it here for study? Think I'm not
vba-versed sufficiently to fashion it out g. Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bob Phillips" wrote in message
...
Max,

Set rng = Application.Inputbox("Select range", Type:=8)

allows the selection of a range mid-macro, and a couple more inputboxes
could be added to get the limits, but that sounds like it would be better
to
throw up a simple form to get the details, easier to control.

Hello Ian,

If you need them only once you can create the unique random integers
with my function UniqRandInt, then select them, push CTLR + c, then ALT
+ e, s, v (copy and paste by values).

Or - if you need this functionality more often - you can:
1. Define a named range "MyCount" for a cell where you enter how many
numbers you want to get. Enter 10 into E1, for example, select E1 and
define that range with Insert/Name/Define.
2. Define a named range "MyRange" for a cell where you specify the max
random number you want to get. Take E2 and enter 20, for example (has
to be = 10!).
3. Define a named range "MyTarget" for a range of cells where you want
the output to be written to. Select E4:E13, for example.
4. Then put my function VBUniqRandInt into a module together with this
sub:
Sub Constant_UniqRandInts()
Dim lr As Long
Dim lc As Long

lr = Range("MyRange")
lc = Range("MyCount")

If Range("MyTarget").Columns.Count 1 Then
Range("MyTarget") = VBUniqRandInt(lc, lr)
Else
Range("MyTarget") =
Application.WorksheetFunction.Transpose(VBUniqRand Int(lc, lr))
End If

End Sub

5. Insert a Push Button into your spreadsheet and connect it to the sub
Constant_UniqRandInts

6. Fire off that button. The random numbers won't change on hitting F9
- just when you push the button.

HTH,
Bernd

I have left Leo's random number generator function alone (although the way
that I am doing it, you could probably remove the need to pass the range
through), and I had to declare RandNum in it as I always use Option
Explicit.

Sub GenerateRandoms()
Dim rng As Range
Dim cell As Range
Dim FirstNum
Dim LastNum

Set rng = Application.InputBox(prompt:="Select range", Type:=8)
If rng Is Nothing Then Exit Sub
FirstNum = InputBox("Provide the starting number")
If FirstNum = "" Then Exit Sub
LastNum = InputBox("Provide the final number")
If LastNum = "" Then Exit Sub

For Each cell In rng
cell.Value = NewRandNum(rng, FirstNum, LastNum)
Next cell

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Max" wrote in message
...
Bob, thanks. Any chance of a sample to work it here for study? Think I'm
not
vba-versed sufficiently to fashion it out g. Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bob Phillips" wrote in message
...
Max,

Set rng = Application.Inputbox("Select range", Type:=8)

allows the selection of a range mid-macro, and a couple more inputboxes
could be added to get the limits, but that sounds like it would be
better
to
throw up a simple form to get the details, easier to control.