Generate Random number

[Serene]

Hi everyone,

I would like to generate some PINs for my end users.
However, these PINs must be of certain length (6 numeric) and they must not be the same as numbers already generated before. I have a list of numbers that have been generated previously. Please advise how these criteria can be included in the random function.

Thanks in advance
Serene

 

[Xiq]

Hi there,
There is the easy way:

=TEXT(INT(RAND()*10^6),"00 00 00")

But this can have a few problems (e.g. each time the file is recalculated, you get a new number).

If this isn't a problem, then your next step is to count/match the result in your table of PINs

 

[Harry Covair]

Hi Serene,
Instead of integrating the existing list in your randomization algorithm, I suggest you check its existence by a simple lookup. If the PIN exists, just recall the randomization routine , if not, the process continues. It could look like:
sub random()
randomization algorithm
call check(PIN)
..........
end sub
function check(PIN)
if lookup = true call random else end function
end function
Regards
Harry

 

[Serene]

Hi Harry,

Thanks for the suggestion.
I am not very proficient in writing subroutines. But actually I would like to system to generate the random PIN and if it exists to generate the next one. How do I ensure that all new pins generated is unique?
Would you mind to show me all of the code?

Thanks again,
Serene

 

[NARAYANK991]

Hi Serene ,

Can you check the following article , which not only gives the code , but also explains how to use it :

http://excelevolution.com/non-repeating-random-numbers/

Narayan

 

[Harry Covair]

Hi Serene,
As I don't know anything of the context, I show you an application schema to put in a module.
Sub testrand()
Dim a As Single
a = Rnd * 1000 'to replace by your PIN calculator.
Call checkrnd(a)
End Sub
Function checkrnd(a)
If Int(a) < 600 Then Call testrand 'I show what you should do if the PIN exists
End Function
Regards
Harry

 

[SirJB7]

Hi, Serene!
If you're going for Harry Covair solution then before calling testrand from other part of the code add a Randomize instruction so as to initialize the seed for generating random numbers. Your code should look like this:

...
Randomize
testrand
...

Hope it helps. For further help position the cursor over the word Randomize and press F1 for displaying the built-in or the online help. There you'll find the available arguments for Randomize and how it works.
Regards!