Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to generate a random uniqe alfanumeric number?
I need to generate a random uniqe alfanumeric number for approx. 1000 lines.
I expect it to be on 4 or 6 digits. Do anyone know how to do that? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to generate a random uniqe alfanumeric number?
I'd start by looking at JE McGimpsey's site:
http://www.mcgimpsey.com/excel/udfs/randint.html Morten wrote: I need to generate a random uniqe alfanumeric number for approx. 1000 lines. I expect it to be on 4 or 6 digits. Do anyone know how to do that? -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to generate a random uniqe alfanumeric number?
Hello,
If your alphanumeric digits are 0..9 and A..Z you have 36 different ones. So 4 digits can represent 36^4 = 1679616 different values. Array enter into A1:A1000 =UniqRandInt(1679616) and into B1 =Conv(A1,10,36,4) and copy down to B1000. My UDF UniqRandInt you can find he http://www.sulprobil.com/html/uniqrandint.html [Set #Const LATE_INITIALISATION = True !] The function Conv is copied below. This approach will not work for 6 digits because 36^6 = 2176782336 which exceeds Excel's LONG representation. I would suggest to generate the 6 digit alphanumeric numbers in a loop which will start over whenever a previously used number is already in use. Regards, Bernd Function Conv(Figure As String, FromBase As Integer, ToBase As Integer, NumberOfDigits As Integer) As String , October 1999 '=conv(Figure,FromBase,ToBase,NumberOfDigits) 'Example: =conv(1234,6,16,6) 'If NumberOfDigits is set to 0 or fewer digits 'than are in the result, the result will be displayed without 'leading zeroes. 'The setup will convert a number from base 2-36 'to another base 2-36 'If the line "Figure = UCase(Figure)" is deleted, it's possible 'to place lower case letters in Digits to cover base 2-62. 'Please keep the above text, if you pass on this routine. Dim Digits As String Dim ToBaseTen As Long Dim Dummy As Variant Dim Counter As Integer Dim Result As String Conv = "Input error" Digits = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ" If ToBase Len(Digits) Then Exit Function Figure = UCase(Figure) For Counter = 1 To Len(Figure) Dummy = Mid$(Figure, Counter, 1) If InStr(Left$(Digits, FromBase), Dummy) = 0 Then Exit Function Else ToBaseTen = ToBaseTen + (InStr(Digits, Dummy) - 1) * (FromBase ^ (Len(Figure) - Counter)) End If Next Counter While ToBaseTen 0 Result = Mid$(Digits, (ToBaseTen Mod ToBase) + 1, 1) & Result ToBaseTen = Int(ToBaseTen / ToBase) Wend If NumberOfDigits = 0 Or NumberOfDigits < Len(Result) Then Conv = Result Else Conv = Right$(String$(NumberOfDigits - Len(Result), "0") & Result, NumberOfDigits) End If End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to generate random number sets | Excel Programming | |||
In excel, I want to generate a random number | Excel Discussion (Misc queries) | |||
How do I generate only one random number without it refreshing? | Excel Worksheet Functions | |||
generate a random number and use if function to generate new data | Excel Worksheet Functions | |||
Generate Random Number Table | Excel Programming |