Generate random list of 6 digit alphanumerics

[SeanB]

Greetings everyone!

Like the subject says, I need to generate a random list of 6 digit alphanumerics. These alphanumerics will be used as product registration codes, so there can be no repeats.

I would like to have a column with several thousand of these codes, so they can be printed on small labels, and one attached to each product.

Preferably, I would like to use only capital letters, and skip the letter 'O' to avoid confusion with zero.

I think I explaned myself well enough there (?). So can anyone help point me in the right direction? My experience with excel is very limited, and I haven't a clue as to how to start attacking this. Any help would be greatly appreciated.

-Sean

 

[jimbojones]

RIGHT - Bear with me on this one. I've been playing a bit and got maybe something to at least get you started:
In Cells A1:A36 I put multiples of 0.028571 (this is 1 divided by 35 - more later).
In B1:B35 I put all your permutations (A-Z excluding O and 0-9).
In C1:H1 I put the Formula =RAND()
In C2 I put the formula "=VLOOKUP(C1,$A$1:$B$36,2,TRUE)" Then copied it across to H2
Then in I2 - =Concatenate(C2,D2,E2,F2,G2,H2)

Tada

Any good (sounds worse then it actually is)

Jim

 

[PaddyD]

Hi - welcome to the board!

Generating the initial list is farly straightforward. Put a letter in a cell (e.g. put a in A1). In another cell, put =code(a1). You should get 97. The code for z is 122. The reverse operation is performed by =char(97) - to get an a etc. You can use this to generate a series of "random" strings"

=CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(97,122))&RANDBETWEEN(1,9)&RANDBETWEEN(1,9)

will give you strings of the form abcd12. The "only" problem you now have is that this method will not guarantee that the strings are uniqiue. This is not necessarily a problem. If you only need to do this once (or if you can create a list that's big enough to cope with demand for the foreseeable future, then once you've generated the list, copy it, paste special - values to get just the numbers, not the formulas, then filter | advanced filter - unique records only to get a list of unique values. Copy this, your final list.

Will it do?

Paddy

EDIT: additional remarks - excel's "random" functions are fairly weak - see this thread & the links for some insight:

http://216.92.17.166/board/viewtopic.php?topic=4990&forum=2
This message was edited by PaddyD on 2002-11-19 14:53

 

[hedrijw]

Here's a VBA approach
This may point you in a direction:

Sub Generate()
Dim all As Variant
all = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", _
"N", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "0", "1", _
"2", "3", "4", "5", "6", "7", "8", "9")
Application.DisplayAlerts = False
For i = Worksheets.Count To 2 Step -1
If Sheets(i).Name = "Random" Then Sheets(i).Delete
Next
Sheets.Add
ActiveSheet.Move After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Random"
Columns("A:A").Select
Selection.NumberFormat = "@"
[a1] = "RANDOM VALUES"
For i = 1 To 20000
Randomize
code = ""
For j = 1 To 6
ptr = Int((UBound(all) + 1) * Rnd)
code = code & all(ptr)
Next j
Worksheets("Random").[a1].Offset(i, 0) = code
Next
End Sub

Obviously some additional work needs to be done, like checking if a value has been previously generated. I started with a blank worksheet each time.
This message was edited by hedrijw on 2002-11-19 15:23
This message was edited by hedrijw on 2002-11-19 15:25