Generating a random 17-character alphanumeric string

[Dan]

EXCEL 2007
I would like to generate a column of random 17-character alphanumeric
strings into cells A1:A100000. I'd also like to continue into column B: eg;
B1:B100000 so that I have 200000 random VINs.
Can anyone help? I can think of some brute-force methods but I'm looking for
an elegant solution. I think it can be done in one column with EXCEL 2007.
Thanks for any help.
Dan

 

[Pete_UK]

Set up a User-defined function, and then copy it down. It will take
quite a while to calculate 200,000 times.

Pete

 

[Gary''s Student]

This macro will fill cells A1 thru A13:

Sub numberit()

'gsnuxx

i = Array(48, 49, 50, 51, 52, 53, 54, 55, 56, 57, _
65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, _
78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, _
97, 98, 99, 100, 101, 102, 103, 104, 105, 106, _
107, 108, 109, 110, 111, 112, 113, 114, 115, 116, _
117, 118, 119, 120, 121, 122)
For k = 1 To 13
v = ""
For j = 1 To 17
n = Evaluate("=randbetween(0,61)")
ch = Chr(i(n))
v = v & ch
Next
Range("A" & k).Value = v
Next
End Sub

Just change the line:

For k = 1 To 13 to suit your needs

 

[Chip Pearson]

Two hundred thousand unique rands will most likely sap Excel's performance
considerably. I have some code that will generate any number of unique
random longs. See http://www.cpearson.com/excel/randomNumbers.aspx, in the
Unique Random Longs section, but I make no promises about calculation times.
You can either modify that code to return alpha-numeric strings or you can
use it as is to generate the longs and then use some formula to create a VIN
out of the long.

Just out of curiosity, are you sure that your VINs can be simply unique
strings? I used to do plant automation programming for General Motors and
for Caterpillar, and I wrote a program to generate VINs. Those VINs had to
be formatted according to, I think, Dept Of Transportation formats.

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Dan]

This is Dan again.
I found a similar question and Max answered it on 11/5/2006. It uses
=CHAR(randbetween.....&......) formula. I played around with it until it
worked.
Since I was in Excel 2007 and I had to get it compatable with Excel 2003, I
had to play with it until I had 3 columns of alphanumerics, 60,000 rows long.
Surprisingly, it did not take but a few seconds to calculate.
Thought I'd throw that in.

 

[Tyro]

Ten to one, you have duplicates

Tyro

This is Dan again.
I found a similar question and Max answered it on 11/5/2006. It uses
=CHAR(randbetween.....&......) formula. I played around with it until it
worked.
Since I was in Excel 2007 and I had to get it compatable with Excel 2003,
I
had to play with it until I had 3 columns of alphanumerics, 60,000 rows
long.
Surprisingly, it did not take but a few seconds to calculate.
Thought I'd throw that in.

 

[Dan]

Chip:
Thanks!
Yes, VINs must be in correct number standardization per NICB, as you stated,
and the check digit must be correct. However, this exercise is to check out
a device that would scan or use keyboard entry to enter a VIN and compare
with an existing database already loaded on the scanner. By using a test
17-character alphanumeric we don't have to worry about the correctness, since
we're just starting to evaluate this technique. We won't be applying the
NHTSA testing right now. We will be moving to this later.


Dan Harrington, Virginia State Police - Auto Theft Unit

 

[Dan]

Tyro:
I checked for dups after you responded.
Interestingly, I had no duplicates.