Randomize from set of values

[Bam]

Hi All,

I was given a task of making a unique set of random (72 x 6 digit)
alphanumeric from the following available letters & numbers.

C F G H J K M N P Q R S T W X Y Z
2 4 5 6 7 9

The format is to be like this CFG245.
All 72 Values must be unique.
They must all be 3 alpha & 3 numeric, in that order.

I had a look at zrandom, but couldn't see it doing this task for me.
Has anyone the ability to code something like this in vba?
Or is this simply beyond excel's capabilities??

Any assistance would be much appreciated.

Cheers,

Bam.

 

[r]

try ...
Sub Unique_Series()
Dim dic As Object
Dim l As Long, s As String, v
Set dic = CreateObject("scripting.dictionary")
dic.Add CreateRandomC, ""
l = 1
Do Until l = 72
s = CreateRandomC
If dic.exists(s) Then
Else
dic.Add s, ""
l = l + 1
End If
Loop

v = dic.keys
For l = 0 To UBound(v)
Cells(l + 1, 2) = v(l)
Next
End Sub

Function CreateRandomC() As String
Dim v, v1, l As Long, s As String
v = Split("C F G H J K M N P Q R S T W X Y Z", " ")
v1 = Split("2 4 5 6 7 9", " ")
For l = 1 To 3
CreateRandomC = CreateRandomC & v(Int(UBound(v) * Rnd))
s = s & v1(Int(UBound(v1) * Rnd))
Next
CreateRandomC = CreateRandomC & s
End Function

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html

 

[Mike H]

Hi,

Try this

Sub marine()
Dim AaRRay As Variant
Dim NaRRay As Variant
Dim r As Long, x As Long
r = 1
AaRRay = Array("C", "F", "G", "H", "J", "K", "M", "N", "P", "Q", "R", "S",
"T", "W", "X", "Y", "Z")
NaRRay = Array("2", "4", "5", "6", "7", "9")
Debug.Print AaRRay(3)
For nums = 1 To 72
Do
For x = 1 To 3
pos = Int((13 - 0 + 1) * Rnd + 0)
mystring = mystring + AaRRay(pos)
Next
For x = 1 To 3
pos = Int((5 - 0 + 1) * Rnd + 0)
mystring = mystring + NaRRay(pos)
Next
Cells(r, 1).Value = mystring
Loop Until WorksheetFunction.CountIf(Range("A1:A" & r), Range("A" & r)) = 1
r = r + 1
mystring = ""
Next
End Sub


mike

 

[r]

sorry, here is the correct version:

Sub Unique_Series()
Dim dic As Object
Dim l As Long, s As String, v
Set dic = CreateObject("scripting.dictionary")
dic.Add CreateRandomC, ""
l = 1
Do Until l = 72
s = CreateRandomC
If dic.exists(s) Then
Else
dic.Add s, ""
l = l + 1
End If
Loop

v = dic.keys
For l = 0 To UBound(v)
Cells(l + 1, 2) = v(l)
Next
End Sub

Function CreateRandomC() As String
Dim v, v1, l As Long, s As String
v = Split("C F G H J K M N P Q R S T W X Y Z", " ")
v1 = Split("2 4 5 6 7 9", " ")
For l = 1 To 3
CreateRandomC = CreateRandomC & v(Int((UBound(v) + 1) * Rnd))
s = s & v1(Int((UBound(v1) + 1) * Rnd))
Next
CreateRandomC = CreateRandomC & s
End Function

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html

 

[Joel]

I got similar results to Mike H. We don't lie doing homework assingments here.

Sub MakeList()

Characters = _
Array("C", "F", "G", "H", "J", "K", "M", "N", "P", "Q", "R", _
"S", "T", "W", "X", "Y", "Z")
Digits = _
Array("2", "4", "5", "6", "7", "9")

Numchar = UBound(Characters)
NumDigits = UBound(Digits)

'format column A where results go to text format
Columns("A").NumberFormat = "@"

'initialize the random number generator
Randomize

For RowCount = 1 To 72
'loop until data doesn't match any previous data
Do
Results = ""
For CharCount = 1 To 3
NewChar = Characters(Numchar * Rnd())
Results = Results & NewChar
Next CharCount

For DigitCount = 1 To 3
NewDigit = Digits(NumDigits * Rnd())
Results = Results & NewDigit
Next DigitCount

'check if results already exists
Set c = Columns("A").Find(what:=Results, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
Range("A" & RowCount) = Results
End If

Loop While Not c Is Nothing
Next RowCount


End Sub

 

[Howard31]

The way to do it, is to make a 2 dimensional array in the 1 dimension enter
the values you have in the second enter random values using Rnd, then sort
the second dimension which will cause the first dimension containing your
values to become mixed up.

 

[Howard31]

The easiest way to do it is to put your values into column 1, in each
corresponding cell in column 2 enter the RAND function, than sort the rows
by column 2, which will mix up column1 you can simply chose the first 3
values, recalculate the sheet which will give you new random values in
column2 than resort, do it for the letters and numbers separately.

 

[Joel]

How do you prevent duplicates?

The easiest way to do it is to put your values into column 1, in each
corresponding cell in column 2 enter the RAND function, than sort the rows
by column 2, which will mix up column1 you can simply chose the first 3
values, recalculate the sheet which will give you new random values in
column2 than resort, do it for the letters and numbers separately.

 

[Bam]

Joel, Mike H, r, howard31,

Thank you all for your contributions.

Joel, Mike H, r - all worked fine for me.

r - your 1st suggestion worked just as well as the 2nd. I saw the difference:
"CreateRandomC = CreateRandomC & v(Int((UBound(v) + 1) * Rnd)) "

I just don't see why you changed it?

Thanks to All.

Amazing response time, by all of you.

Cheers,

Bam.

 

[r]

:

[cut]

r - your 1st suggestion worked just as well as the 2nd. I saw the difference:
"CreateRandomC = CreateRandomC & v(Int((UBound(v) + 1) * Rnd)) "

I just don't see why you changed it?

because the first you would have had in the codes 9 and Z
regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html