Help With Random Numbers

[IceBox]

I have worked with random numbers quite a bit, but one aspect has always puzzled me. And now I need to use that aspect...

 

Basically, I need Macro Express to generate x/3 amount of unique random numbers out of a range from 1 to x. x will change daily. Let me put this into more concrete terms:

 

I get an Excel file every day that has a variable amount of lines on it. I need to take 1/3 of those lines (at random) and copy them to another tab in that workbook. So if there are 240 lines, I need to copy 80 of them at random. Obviously, if I generate 80 random numbers, there is a chance that some numbers will duplicate and I need them to be all unique.

 

Is there a way to generate unique random numbers?

 

I have already programmed a macro that generates 2 unique random numbers using code similar to this:

 

Variable Set Integer %N1% with a Random Number
Repeat Until %N2% <> %N1%
    Variable Set Integer %N2% with a Random Number
Repeat End

 

This method worked great for just 2 numbers, but what about for 80? That could get REALLY long!!!

 

Any help is much appreciated!

[kevin]

You could put the numbers into a string variable and then do an If Variable Contains. Just make sure you add some separation before putting the number in the string variable.

[randallc]

Hi, Iceman, Kevin,

 

I'm still learning; I woould have to use the registry or similar for this; is there a better way?

 

// INITIALISER

Variable Set String %T1% "HKEY_CURRENT_USER\Software\Professional Grade Macros\PGM Functions\Swap\"

Variable Modify String: Save %T1% to Environment Variable

// Get random

Repeat Start (Repeat 80 times)

  Repeat Until %N1% <> %N1%

    Variable Set Integer %N1% with a Random Number

    Delay 100 Milliseconds

    Variable Modify Integer: Convert %N1% to text string %T2%

    Variable Set String %T5% "0"

    If Variable %N1% > 99

      Variable Set String %T5% ""

    End If

    If Variable %N1% < 10

      Variable Set String %T5% "00"

    End If

    Variable Modify String: Append %T2% to %T5%

    If Variable %T3% does not contain variable %T5%

      Write Registry Integer: "%Reg%Random%N2%"

      Repeat Exit

      Else

      Text Box Display: Repeat NOT exit

    End If

  Repeat End

  Variable Modify String: Append "%T5%," to %T3%

Repeat End

Text Box Display:

Variable Set String %T1% ""

Variable Modify String: Save %T1% to Text File

Repeat Start (Repeat 80 times)

// Read N1th random number from Random%N1% in Registry; ---into %N2%

Read Registry Integer: "%Reg%Random%N1%"

// If you were processing text files, or excel sheet in csv format,

Text File Begin Process: "fivehundred.txt"

Variable Modify String: Append %T1% to Text File

Text File End Process

Repeat End

Program Launch: "NewFile.txt"

<REM2:INITIALISER><TVAR2:01:01:HKEY_CURRENT_USER\Software\Professional Grade Macros\PGM Functions\Swap\><TMVAR2:19:01:00:000:000:Reg><REM2:Get random><REP3:01:000001:000001:00080:1:02:><REP3:08:000002:000002:0001:1:01:N1><IVAR2:01:06:240><DIS:<IMSD:100><NMVAR:05:01:0:0000002:0:0000000><TVAR2:05:01:0><IFVAR2:2:01:4:99><TVAR2:05:01:><ENDIF><IFVAR2:2:01:3:10><TVAR2:05:01:00><ENDIF><TMVAR2:08:05:02:000:000:><IFVAR2:4:03:8:T5><REGWINT:1:%Reg%Random%N2%><EXITREP><DIS:<ELSE><DIS:<TBOX4:T:1:CenterCenter000278000200:000:Repeat NOT exitN1=new RandomNumber=%N1%
N2= Number we're doing=%N2%
N3=%N3%

%T3%

><ENDIF><ENDREP><TMVAR2:07:03:00:000:000:%T5%,><ENDREP><TBOX4:T:1:CenterCenter000278000200:000:%T3%><DIS:<TVAR2:01:01:><DIS:<TMVAR2:17:01:00:000:000:C:\WinNeeded\NewFile.txtF><DIS:<REP3:01:000001:000001:00080:1:01:><REM2:Read N1th random number from Random%N1% in Registry; ---into %N2%><DIS:<REGRINT:2:%Reg%Random%N1%><REM2:If you were processing text files, or excel sheet in csv format,><DIS:<BTFBEG:001:N00002:000001:C:\WinNeeded\fivehundred.txt><DIS:<TMVAR2:20:01:00:000:000:C:\WinNeeded\NewFile.txtT><DIS:<BTFEND><DIS:<ENDREP><DIS:<LAUNCHDEL2:0:01C:\WinNeeded\NewFile.txt>

 

Best, Randall

[kevin]

Here is an example. No registry needed.

 

This is very flexible. Change the values at the beginning of the macro. You may want to have your macro calculate the maximum number of random numbers to generate.

 

The numbers are displayed at the end of the macro AND they are written into the file specified by %T5%. You can use the Text File Begin/End Process command to read from the file and do your copying.

 

Variable Set String %T1% ""

// Set maximim random number
Variable Set Integer %N3% to 999
// Set number of digits per number
Variable Set Integer %N4% to 3
// Maximum number of random numbers to generate
Variable Set Integer %N5% to 100
// Work filename
Variable Set String %T5% "%TEMP%\work.txt"
// If work file already exists, delete it
If File Exists "%T5%"
 Delete File or Files: "%T5%"
End If

// ----------------------------
//   Repeat until the correct number of unique random numbers is calculated
// ----------------------------
Repeat Until %N1% >= %N5%
 Variable Set Integer %N2% with a Random Number
 Variable Set String %T2% "%N2%"
 Variable Modify String: Pad Left %T2%
 Replace " " with "0" in %T2%
 
 If Variable %T1% does not contain variable %T2%
   Variable Modify String: Append "%T2%  " to %T1%
   Variable Modify Integer: %N1% = %N1% + 1
   Variable Modify String: Append %T2% to Text File
 End If
Repeat End
Text Box Display: T1

<TVAR2:01:01:><REM2:><REM2:Set maximim random number><IVAR2:03:01:999><REM2:Set number of digits per number><IVAR2:04:01:3><REM2:Maximum number of random numbers to generate><IVAR2:05:01:100><REM2:Work filename><TVAR2:05:01:%TEMP%\work.txt><REM2:If work file already exists, delete it><IFOTH:01:2:%T5%><DOFILE:08:NN:%T5%>><ENDIF><REM2:><REM2:----------------------------><REM2:  Repeat until the correct number of unique random numbers is calculated><REM2:----------------------------><REP3:08:000005:000002:0001:1:01:N5><IVAR2:02:06:%N3%><TVAR2:02:01:%N2%><TMVAR2:14:02:00:%N4%:000:><TMVAR2:21:02:01:000:000: 0><REM2:><IFVAR2:4:01:8:T2T><TMVAR2:07:01:00:000:000:%T2%  ><NMVAR:01:01:1:0000001:2:0000001><TMVAR2:20:02:00:000:000:%T5%T><ENDIF><ENDREP><TBOX4:T:1:CenterCenter000739000365:000:T1%T1%>

[randallc]

Hi Kevin,

 

Thanks!

 

It still seems somehow indirect and non-intuitive using all this unreadable T1/N1 gear!

 

I just want to use names, as for environment variables (but can't read them in script editor mode) for strings, or registry integers (acn read!) for integers!

 

Perhaps I can write them this way for easier understanding, then convert to the "better" code!

 

Do you use the "text process" for all this sort of "array" loop processing? -I realise it is efficient (Is the file kept open by the process command, as I realise it is fast too?)

 

Best, Randall

[IceBox]

Thanks Guys! VERY VERY helpful!

[kevin]

Randall:

I just want to use names ... for ... variables

Yes, I would prefer 'named' variables too. I normally create a 'key' using remarks at the top of the macro to let me know what the variables are used for.

Do you use the "text process" for all this sort of "array" loop processing? -I realise it is efficient (Is the file kept open by the process command, as I realise it is fast too?)

The file is opened once by the Text File Begin Process and closed by the Text File End Process. In this case we're talking about copying files. By my estimation the entire overhead of the Text File Begin/End Process loop would be less than the overhead of a single file copy.

 

When comparing Text File Begin/End Process with registry access, keep in mind that the registry is just one big file. It is true that Windows 'caches' reads from and writes to the registry but there is still file overhead. And, in this case, because of the file sizes, the file overhead of accessing the registry is much greater than the file overhead of Begin/End Process loop.

[IceBox]

Hi guys. After creating the macro I needed, I pulled the unique random number coding out and created this "generic" macro if anybody is interested. The way it is set up right now, the macro:

 

1. Asks you to set a range of numbers to choose from (works for any numbers up to 999,999,999)

2. Asks you to select the amount of unique random numbers to be selected from this range

3. Opens Notepad (You may have to change your path)

4. Types the selected numbers in Notepad in the form of a list.

 

Thanks alot for the ideas on how to accomplish this. I'm not sure if there are many practical applications out there, but it was definitely a must in my office!

Unique_Random_Numbers.mex

[randallc]

Hi IceBox,

 

Thanks! I always appreciate seeing how others do their coding; I always learn something!

 

Best, Randall

[joe]

Hello IceBox!

 

3. Opens Notepad (You may have to change your path)

For future reference: because notepad.exe is installed in the system path, Macro Express will locate it without requiring a fullpath name in the Program/Path Name field.

[IceBox]

Thanks Joe, that's great information!

[thefluxster]

All,

 

Your ideas for this random number generator are great! I'll definitely implement portions of this process in my own macros.

 

Using Notepad with the "TextType" function seems to work fine for the first few thousand numbers... However, if the number sequences get very long or if the list of numbers gets very long, it seems to start spitting out a lot of incorrect numbers - almost as if the <ENTER> function isn't being added correctly.

 

To get around this, you can do one of two things:

 

1. insert the "Wait Text Playback" command after the "Text Type" command (which will, of course, DRASTICALLY slow down the macro)

2. output your data to a text file.

 

Either way, if the list of numbers you are generating is very large, it seems to take forever to compare newly generated numbers against the existing list. Perhaps someone would take up the challenge to increase the speed of this macro by making it use an array, or better yet, a little recursion!

[randallc]

Hi thefluxter,

 

Interesting to hear your thoughts for us home hobbyists!

 

I think Kevin was elegant in text file process for this, in the same thread; on top of what I had suggested; the repeat loop is probably as efficient as Macro Express gets?

 

Kevin's random macro

 

Best, Randall

[kevin]

Using Notepad with the "TextType" function seems to work fine for the first few thousand numbers...

What happens is Macro Express can pump information out faster than Notepad can accept it. Notepad starts skipping keys.

 

To make the Text Type function more reliable, you may want to consider adjusting the "Use Text Type delay" option in Options, Preferences, Delays. I have this value set to 10 microseconds. I have not tried this with several thousand lines however.

 

Another technique I have used is to put a slight delay every time an <ENTER> is typed.

which will, of course, DRASTICALLY slow down the macro

Sometimes you have to slow a macro down to match the capabilities of the program you are automating. Better slow than unreliable.

[Nicolas]

if you want a list of random numbers between 1 and N

and

if %Tx% variables can accept strings of length N (I don't know if %Tx% variables have a maximum length ?)

 

I suppose it would go faster to use something like that :

1) fill %T1% with N+1 characters "0" (or any other character)

2) %N1% = random value with max N

3) if %N1%th character in %T1% = "1", back to second step

4) set %N1%th character in %T1% to "1"

6) add %N1% to your list (or skip this step and parse %T1% at the end)

7) back to step 2 until you have the requested number of random values

 

I suppose it should be faster to look at a given character in a string rather than using the "If Variable contain" command ... moreover, with this algorithm your random numbers are sorted with no extra time

 

here is a code, quickly written (I'm sorry but it is 3:00 am ) ... to replace a character in a string, I cut it in two substrings (before and after the character to replace) : is there any better solution ?

 

// %N3% random numbers with max %N4%
// sorry but it's late so results are simply stored (sorted) in %T3%
// ------
Variable Set Integer %N3% to 20
Variable Set Integer %N4% to 30

If Variable %N3% > variable %N4%
 Pause: Complex
 Macro Stop
End If

Variable Set String %T1% ""
Repeat with Variable using %N4%
 Variable Modify String: Append "0" to %T1%
Repeat End

Variable Set String %T3% "non sorted list :"
Repeat Until %N3% = 0
 // %N1% is a random number between 1 and %N4%
 Variable Set Integer %N1% with a Random Number
 If Variable %N1% <> 0
   Variable Modify String: Copy Part of %T1% to %T2%
   If Variable %T2% = "0"
     // %N1% is a new random number 
     Variable Modify String: Append "  %N1%" to %T3%
     // we replace the relevant character in %T1% from 0 to 1
     Variable Modify Integer: Inc (%N1%)
     Variable Modify String: Copy Part of %T1% to %T2%
     Variable Modify Integer: Dec (%N1%)
     Variable Modify String: Delete Part of %T1%
     Variable Modify String: Append "1" to %T1%
     Variable Modify String: Append %T2% to %T1%
     // we continue until we found %N3% random values
     Variable Modify Integer: Dec (%N3%)
   End If
 End If
Repeat End
Pause: Complex

// list of sorted random values in %T3%
Variable Set String %T3% "sorted list :"
Repeat with Variable using %N4%
 Variable Modify String: Copy Part of %T1% to %T2%
 If Variable %T2% = "1"
   Variable Modify String: Append "  %N1%" to %T3%
 End If
Repeat End
Pause: Complex

 

<REM2:%N3% random numbers with max %N4%><REM2:sorry but it's late so results are simply stored (sorted) in %T3%><REM2:------><IVAR2:03:01:20><IVAR2:04:01:30><REM2:><IFVAR2:5:03:4:N4><PAUSE2:Center,Centersorry but I cannot find %N3% different values between 1 and %N4%T><MSTOP><ENDIF><REM2:><TVAR2:01:01:><REP3:05:000001:000001:0004:0:01:><TMVAR2:07:01:00:000:000:0><ENDREP><REM2:><TVAR2:03:01:non sorted list :><REP3:08:000001:000002:0003:0:01:0><REM2:%N1% is a random number between 1 and %N4%><IVAR2:01:06:%N4%><IFVAR2:2:01:2:0><TMVAR2:10:02:01:N01:001:><IFVAR2:1:02:1:0><REM2:%N1% is a new random number ><TMVAR2:07:03:00:000:000:  %N1%><REM2:we replace the relevant character in %T1% from 0 to 1><NMVAR:08:01:0:0000001:0:0000000><TMVAR2:10:02:01:N01:N04:><NMVAR:09:01:0:0000001:0:0000000><TMVAR2:11:01:00:N01:N04:><TMVAR2:07:01:00:000:000:1><TMVAR2:08:01:02:000:000:><REM2:we continue until we found %N3% random values><NMVAR:09:03:0:0000001:0:0000000><ENDIF><ENDIF><ENDREP><PAUSE2:Center,Center%T3%T><REM2:><REM2:list of sorted random values in %T3%><TVAR2:03:01:sorted list :><REP3:05:000001:000001:0004:1:01:><TMVAR2:10:02:01:N01:001:><IFVAR2:1:02:1:1><TMVAR2:07:03:00:000:000:  %N1%><ENDIF><ENDREP><PAUSE2:Center,Center%T3%T>

 

you have to change %N3% and %N4% with the values you want and this sample will take %N3% different random values between 1 and %N4%, and display them (first in the order they were found then sorted)

[randallc]

Hi Nicholas!

 

Fantastic! - If you take the text file part out of Kevin's, he produces 3000/5000 random numbers in 2 seconds; yours about 0.5 secs by my reckoning.

 

They both use large strings, though, and I guess string and environment constaints would affect things; yours 8 secs for 30,000/50,000 (his longer ? 60secs?) , but I did not bother waiting for 300,00/500,000 on yours!

 

What would it be like to do it with a text file instead of a string, so its unlimited?

 

(Can you change your "pause" to Text Box Display so we can see more (and enlarge it "manual"?)

 

Best, Randall

 

PS where did you learn to do that!?

[Nicolas]

PS where did you learn to do that!?

learn ? hummm ... I'm a software engineer / project manager ... I suppose it helps

 

regarding my algorithm, it's really a pity to have to split a string in two substrings and re-append them when I simply want to replace ONE character with another one : a string being an array of characters, you can replace a character in constant time ! so it would be REALLY faster if we had a "replace character at position %N%" function (maybe it exists and I did not find it ?) ... I think it is the bottleneck in my algorithm (hum I'm not sure if I can say that in english)

 

anyway ... thanks for the figures Randall ! it's really interesting to compare algorithms relative performances ! but we can't easily use a file because we can't replace a character in a file without reading all the string / splitting / writing all the string ... would be even longer