Six digit random number generator?

Hi Bjohnston,

Formula in B2 =RANDBETWEEN(0,9)

Fill right to G2 then fill down.

Formula in H2 (and fill down) =B2&C2&D2&E2&F2&G2

Formula in I2 (and fill down) =COUNTIF(H$1:H2,H2)

That will check for duplicates. Any value greater than 1 is not unique.

Now select Column H, copy then Menu > Edit > Paste Formula Results

That will freeze the values.

Regards,

Ian.

Here's a way you can use:

make the first row a header row.

A2=NUMTOBASE(ROW()+1000×RANDBETWEEN(0,999), 10, 6)

B2=COUNTIF(A, A2)

select cells A2 and B2, copy

select cells S2 thru the end of column B, paste

B1="Duplicates = "&COUNTIF(B, ">1")

I think using this method unique IDs are guaranteed, and I added the formulas in column B to confirm this.

Yellowbox wrote:

bias towards leading zeros???

Hi Ian,

I don't think that introduces bias:

=RIGHT("00000"&RANDBETWEEN(1,999999),6)

And I suspect Wayne's approach does not completely eliminate the possibility of duplicates.

So probably best to have the check column just in case.

SG

Hi Bjohnston,

Locking will not stop the RANDBETWEEN function from recalculating with every little change to the document.

You can "freeze" the results by replacing formulas with the last calculated values.

Select the whole table (click on the "Bullseye" top left).

Menu > Edit > Copy

Menu > Edit > Paste Formula Results.

Regards,

Ian.

P.S. before you do that, you may want to Save as Template to preserve the formulas for future use.

Ian.

Hi Ian,

I wondered why you were not just using

=RANDBETWEEN(0,999999)

until I tried it and got 2169 as a result. Not the same as 002169 for these purposes!

I hadn't thought of checking for duplicates.

Here is a solution that does it with one formula.

=ROUND(RAND(),6)×1000000

Edit: Whoops! I will still return a 5 digit #.

quinn

I think the method I post does produce unique IDs because use the most significant three digits to hold a random number between 0 and 999 (RRR) and the I use the least significant three digits to hold the row number (rrr)

RRRrrr

even if RRR repeats, rrr will be unique because that row number only exists once

the best way to force a certain number of digits (leading zeros) is to use the function:

formula I used here is:

=NUMTOBASE(ROW(), 10, 6)

Or:

In A2, down:

=RIGHT("00000"&INT(RAND()×1000000),6)

The check for duplicates in B2, filled down:

=COUNTIF(A,A2)

SG

Hi Wayne,

I am glad to know this. Never would have found that function on my own.

quinn

Hi quinn,

Yes, I thought about =RANDBETWEEN(0,999999) and padding with leading zeroes, but that would bias towards leading zeros???

Regards,

Ian.

Thanks for explanation. So no duplicates but possibly some non-randomness introduced by using row numbers?

SG

Yup