PLS HELP: random unique-non repeated numbers.

Hi everyone!

I have this project for a client who wants me to produce 200,000 pcs of scratch cards (for mobile phones) and the last time I tried generating 6,000 "random unique-non repeated numbers" using randbetween there were about 150 duplicates that came up. I would like to know how if there is really a way in excel to do this..

any help will be appreciated..

by the way, below is a sample:

SERIAL PIN

20319422 ACA95368


thank you!!

It's a bit slow generating 200,000 numbers...

Please Login or Register  to view this content.

Originally Posted by JBeaucaire

Try this, put this formula in A1:

=LEFT(INT(RAND()*10000000000), 8) + 0


Put this B1:

=CHAR(RANDBETWEEN(1,26)+64) & CHAR(RANDBETWEEN(1,26)+64) & CHAR(RANDBETWEEN(1,26)+64) & TEXT(RANDBETWEEN(1,99999), "00000")



Copy both cells down 6000 rows. Pretty unlikely you'll get any dupes.


I've asked the moderators to move this thread to the appropriate forum.

My bad I posted on the wrong thread.

THANK YOU!! by the way, will I get any duplicates if I copy it down to 200,000 cells? or it will limit me to 6000 (w/o duplicates)?

Originally Posted by dangelor

It's a bit slow generating 200,000 numbers...

Please Login or Register  to view this content.

thank you dangelor! I manage to come up with 200,000 numbers using your code but it was reallyyyy slow as you said. On your code it says 6000 so I would like to know if I stretch it to 200,000 will I be likely to get duplicates? Also, how can I make this alpha-numeric?

You can easily check for duplicates by sorting the output A-Z, adding a helper column with a formula =A2-A1 and copy down, then copy the helper column and paste as values, and finally sort the helper column A-Z. if there are any duplicates, you will see a zero value at the top of the list.

As for alpha-numeric, not sure... would the alpha characters always be in the same position in the string?