Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What Should I do in this situation | New Users to Excel | |||
how can i set up rand or rand between to give only -1 or 1? | Excel Worksheet Functions | |||
"Tricky Situation" - Validation Formula | Excel Discussion (Misc queries) | |||
Nested IF in this situation | Excel Worksheet Functions | |||
Offset Situation | Excel Discussion (Misc queries) |