Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Generating a random 17-character alphanumeric string
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Generating a random 17-character alphanumeric string
Set up a User-defined function, and then copy it down. It will take
quite a while to calculate 200,000 times. Pete On Feb 13, 11:52*pm, Dan wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Generating a random 17-character alphanumeric string
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 -- Gary''s Student - gsnu200769 "Dan" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Generating a random 17-character alphanumeric string
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Generating a random 17-character alphanumeric string
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" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Generating a random 17-character alphanumeric string
Ten to one, you have duplicates
Tyro "Dan" wrote in message ... 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" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Generating a random 17-character alphanumeric string
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 "Chip Pearson" wrote: 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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Generating a random 17-character alphanumeric string
Tyro:
I checked for dups after you responded. Interestingly, I had no duplicates. "Tyro" wrote: Ten to one, you have duplicates Tyro "Dan" wrote in message ... 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
random alphanumeric string | Excel Worksheet Functions | |||
Generating Random numbers. | Excel Discussion (Misc queries) | |||
Alphanumeric random number? | Excel Discussion (Misc queries) | |||
How do I replace last numeric string from a alphanumeric string? | Excel Discussion (Misc queries) | |||
Generating Random Number from a set of numbers | Excel Discussion (Misc queries) |