tricky rand situation
 

i am using the rand function to generate a number which is to be given
to each customer who fills in my form. Their number and details will be
placed in a look up table. How could i prevent the same number coming
up out of the 10,000,000 possible outcomes when i multiply the rand
function by 10,000,000.


---
Message posted from http://www.ExcelForum.com/

tricky rand situation
 

Noctos,

Check the initial result against your record table: if you find the
value, generate another random number, and continue until you don't
find it in the table. If you use the MATCH function with the last
parameter = FALSE (to find exact matches), it will return an error
when you have a unique number.

HTH,
Bernie
MS Excel MVP


"Noctos " wrote in message
...
i am using the rand function to generate a number which is to be
given
to each customer who fills in my form. Their number and details will
be
placed in a look up table. How could i prevent the same number
coming
up out of the 10,000,000 possible outcomes when i multiply the rand
function by 10,000,000.


---
Message posted from http://www.ExcelForum.com/

tricky rand situation
 

yea the thing is the user will be doing this by thereselves so i won't
be there to keep on checking if the rand number to do. I think the best
way is to use vba. The rand number will be generated and this number
can be compared to the table using the match value if a value is found
then the rand number could be refreshed like a loop until a unique
value is found and this value is then displayed. Does anyone know how
to translate this into vba code


---
Message posted from http://www.ExcelForum.com/

tricky rand situation
 

Noctos,

The sub below will store the unique number appearing in cell A1 in
column B of the same sheet.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim boolMatch As Boolean
boolMatch = True

While boolMatch
Range("A1").Value = "'" & Format(Rnd() * 10000000, "0000000")
If Application.WorksheetFunction.CountIf(Range("B:B") ,
Range("A1").Value) = 0 Then
boolMatch = False
Range("B65536").End(xlUp)(2).Value = "'" & Range("A1").Text
End If
Wend

End Sub


"Noctos " wrote in message
...
yea the thing is the user will be doing this by thereselves so i
won't
be there to keep on checking if the rand number to do. I think the
best
way is to use vba. The rand number will be generated and this number
can be compared to the table using the match value if a value is
found
then the rand number could be refreshed like a loop until a unique
value is found and this value is then displayed. Does anyone know
how
to translate this into vba code


---
Message posted from http://www.ExcelForum.com/

tricky rand situation
 

Why generate a random number. Sounds like all you need is a unique number

? Format(now,"yymmddhhmmss")
040102150108



--
Regards,
Tom Ogilvy


Noctos wrote in message
...
yea the thing is the user will be doing this by thereselves so i won't
be there to keep on checking if the rand number to do. I think the best
way is to use vba. The rand number will be generated and this number
can be compared to the table using the match value if a value is found
then the rand number could be refreshed like a loop until a unique
value is found and this value is then displayed. Does anyone know how
to translate this into vba code


---
Message posted from http://www.ExcelForum.com/

tricky rand situation
 

not entirely sure how to use your unique number tom but bernie vb is
saying there is an error in these two lines

If Application.WorksheetFunction.CountIf(Range("B:B") ,
Range("A1").Value) = 0 Then


---
Message posted from http://www.ExcelForum.com/

tricky rand situation
 

You are a victim of email wordwrap. It should all be one line, or put in a
line continuation character (space underscore):

If Application.WorksheetFunction.CountIf(Range("B:B") , _
Range("A1").Value) = 0 Then

--
Regards,
Tom Ogilvy




Noctos wrote in message
...
not entirely sure how to use your unique number tom but bernie vb is
saying there is an error in these two lines

If Application.WorksheetFunction.CountIf(Range("B:B") ,
Range("A1").Value) = 0 Then


---
Message posted from http://www.ExcelForum.com/

tricky rand situation
 

"Tom Ogilvy" wrote...
Why generate a random number. Sounds like all you need is a unique number

? Format(now,"yymmddhhmmss")
040102150108

While it may be rare, this technique can cause problems if multiple users
could be using different copies of the workbook (or its forms)
simultaneously. The problem would be similar to generating unique invoice
numbers when there are multiple simultaneous users. If that were so, then
some form or centralized generation of unique numbers is unavoidable.

tricky rand situation
 

Assuming a single user per customer
Easily solved by appending some unique identification information for the
customer.

All schemes would be made more robust by using a central issuing authority,
but that didn't appear to be the intent of the OP.

--
Regards,
Tom Ogilvy

Harlan Grove wrote in message
...
"Tom Ogilvy" wrote...
Why generate a random number. Sounds like all you need is a unique
number

? Format(now,"yymmddhhmmss")
040102150108

While it may be rare, this technique can cause problems if multiple users
could be using different copies of the workbook (or its forms)
simultaneously. The problem would be similar to generating unique invoice
numbers when there are multiple simultaneous users. If that were so, then
some form or centralized generation of unique numbers is unavoidable.

tricky rand situation
 

"Tom Ogilvy" wrote...
....
All schemes would be made more robust by using a central issuing authority,
but that didn't appear to be the intent of the OP.
....

Perhaps. In my own experience, unique ID generation is highly susceptible to
Murphy's Law.