Thread: 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...

Sub UniqueRandomNumbers()
    'Creates a list of numbers starting in A1

    Dim HowMany     As Long
    Dim a           As String
    Dim ncRandList  As New Collection
    Dim r           As Range
    Dim x           As Long

    HowMany = 6000
    Randomize
    On Error Resume Next
    Do While ncRandList.Count < HowMany
        a = Int(Rnd() * 99999999) + 1
        ncRandList.Add a, CStr(a)
    Loop
    On Error GoTo 0

    Application.ScreenUpdating = False
    For Each r In Range("a1").Resize(ncRandList.Count, 1)
        x = x + 1
        r = ncRandList(x)
    Next r
    Application.ScreenUpdating = True
End Sub

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...

Sub UniqueRandomNumbers()
    'Creates a list of numbers starting in A1

    Dim HowMany     As Long
    Dim a           As String
    Dim ncRandList  As New Collection
    Dim r           As Range
    Dim x           As Long

    HowMany = 6000
    Randomize
    On Error Resume Next
    Do While ncRandList.Count < HowMany
        a = Int(Rnd() * 99999999) + 1
        ncRandList.Add a, CStr(a)
    Loop
    On Error GoTo 0

    Application.ScreenUpdating = False
    For Each r In Range("a1").Resize(ncRandList.Count, 1)
        x = x + 1
        r = ncRandList(x)
    Next r
    Application.ScreenUpdating = True
End Sub

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?