Creation of data using a formula?

[vk7]

I would like to generate 1 Million Random Numbers having a prefix of AA and having the number length of 16. For example it should look something like "AA1234567898616720". Is there a way to get this done via some formula in excel? Only one condition should be all the 1 Million data should be unique.

 

[Hui]

Try in B1: ="AA" & Randbetween(0,9) & TEXT(ROUND(1000000000000000*RAND(),15),"###############")

copy it down to B1000000

Chances of getting a repeat number are slim
You can check with
=SUMPRODUCT(1/COUNTIF(B1:B1000000,B1:B1000000))
it should be 0 if there are no duplicates

If there are press F9, recheck

 

[vk7]

@Hui, thank you for helping me out.

I see the random values are getting generated. But the numeric value is not of length 16 digits for all the records. In some it is 16 digits and some it is 15 digits. Any idea why is this happening?

 

[Hui]

Whoops
="AA" & Randbetween(0,9) & TEXT(1E15*RAND(),"00#############")

 

[vk7]

@Hui, it helps and this is what it is needed. One final question.

AA1234567898616720

If I only wanted to generate the random numeric characters that are marked in RED color assuming the first 4 characters are fixed. How could I do that?

AA1234567898616720

Same for the above too, but if I wanted to generate random numbers between some given ranges how can we do that? for example ranges between (5000000000 to 5999999999) ? Could you please help me with the example for the same?

 

[Hui]

="AA123456" & RANDBETWEEN(5000000000,5999999999)

This doesn't need the Text function as the Rand function only generates a 10 digit number , it can generate a 1-9 digit number

In previous examples you could have used:
="AA" & RANDBETWEEN(100000000000000,999999999999999)
but that excludes random numbers less than 100000000000000 eg 100 which should be 000000000000100