Creating Random, Unique 5-Digit Numbers


Would it be possible to receive some assistance?

In Calc, we’d like to create a macro which fills Column A with 100,000 unique, 5-digit numbers.

In this scenario, the number “00000” is valid. So, every possible 5-digit number will appear at some random place in the list. No 5-digit number will appear twice. There will be leading zeros, although that may be a formatting issue, not a macro issue.

Can this be accomplished with a simple macro, or is something more robust required?

Thanks so much!

Tony Collette

Create the sequence 00000 through 99999 and bring it into random order. This you can do using an adjacent column filled with =RAND() and sorting both the columns along the second one. Needs about 1 s to run.

Why do you think to need a “macro”? The task is very simple in interactive mode. Doing it based on a specific program is rather complicated and inefficent (concerning time and intermediary storage). Doing it basically in the same way as you might do it interactively would require to use a sorting algorithm. You may look for an appropriate implementation of quicksort. Simple enough to write it down yourself “on the fly” may only be bubblesort which is of O(n^2). Using a bubblesort from my toolbox I started the experiment with reduced size of 1000. It needed about 17 s on my 6 year old PC. 100 times the size would (roughly, theoretically) need 10000 times the time: 2 days.

There is a not so well known O(n) algorithm, too, but it isn’t easily implementable in BASIC since it needs bit-/bytewise access to the internal represenattion of numbers.

A way to create your numbers in random order from the beginning might use “Floyd’s Algorithm”. See technique C from this tutorial. I wouldn’t expect that to be efficient either and didn’t try it yet.

Using the LibO API to some depth you can also simulate the interactive way programmatically. However, sorting based on a sort descriptor can only work on cell ranges, not on declared array-variables directly filled with your numbers.

My advice: Do it interactively.

A working quicksort algorithm:

(only needs SWAP).

Thank you Lupp and Mike! I appreciate your suggestions.

Lupp – creating the list of all possible 5-digit numbers in Column A was easy. Can you tell me how to put =RAND() in every cell down Column B? There would be 100,000 of them.

Mike – Thanks for pointing to this sorting program. Being a complete noob, how would I get, or where would I put, that basic code? Would it be the contents of a macro? I’m a bit lost.

Thanks to both you!

Tony Collette

(The hint concerning quicksort was addressed to me, suppoesdly.)
Put =RAND() into the adjacent cell right of the first number. At the right bottom of that cell you will find a tiny square (“drag handle”) then. Aim the mouse pointer at that exactly and lef doubleclick. Done