Hi Andy, 1. This is a simple conversion from base 10 to base-N, where N is the number of characters in your alphanumeric set of symbols - mapping a "random" subset of the integers from 1 to N^6. By "random" I assume you do not wish to create consecutive integer values, but desire some hopping around without duplication. This is easily accomplished using a linear congruence "randomization" equation, based on the internal Get(RecordID), for example.
Here's a sample calculation for the field "AlphaID" Let( [ string = "BCDFGHJKLMNPQRSTVWXZ0123456789" ; len = Length( string ) ; ID = Mod( 58962061 * Get(RecordID) ; len^6 ) ] ; Middle( string ; Mod( Div( ID ; len^5 ) ; len ) ; 1 ) & Middle( string ; Mod( Div( ID ; len^4 ) ; len ) ; 1 ) & Middle( string ; Mod( Div( ID ; len^3 ) ; len ) ; 1 ) & Middle( string ; Mod( Div( ID ; len^2 ) ; len ) ; 1 ) & Middle( string ; Mod( Div( ID ; len ) ; len ) ; 1 ) & Middle( string ; Mod( ID ; len ) ; 1 ) ) ====== 2. 24 symbols into 24 fields with no repeats within a record -> a Permutation of 24 items. Since there is a bijective mapping between the Permutations of 24 items and the unique Cantor representation of the non-negative integers less than 24! (24-factorial), a (random) integer between 0 and 24! - 1 can be used to create the non-repeating 24 entries in a record. Furthermore, since you want each of these 10,000+ records to contain a unique (non-duplicated) Permutation, the "Permutation Number" for each record can be generated using a linear congruence method to guarantee uniqueness among the 10,000+ records. ====== As I'm writing this, I see you have received a "solution" from another source; thus I will not complete the answer to #2. What is missing is a custom function which converts an integer into the corresponding Permutation of N symbols. Best wishes, Jason L. DeLooze Annapolis, MD USA "God created the integers, all else is the work of man" On 12/29/07 at 10:38 AM -0500, Andrew Kappy wrote: >I'm looking for help with two random number problems. > >1. I need to generate 750,000 alpha-numerics, 6 characters long, excluding >certain letters. I have no problem generating as many random numbers as I >want, using the RANDOM function, but getting down to 6 alpha-numerics without >duplicates is the problem. I've generated 1.5 million random numbers and >translated them to integers. I've also built a numbered table of included >alpha characters. I've tried various calculations to grab 4 numbers from the >larger random number and combine them with two of the letters via >calculations. The result is way too many duplicates. > >2. I need to generate over 10,000 records of 24 fields, with the numbers 1-24 >(or letters A-X) randomly distributed through each record, with no repeats >within a record and no distribution repeated within the 10,000 records. > >Any suggestions will be greatly appreciated. > >Andy Kappy
