Generate Random 4 Digit Safe Codes Without Any Character Repetition Within Each Code

be4con

New Member
Joined
Feb 7, 2019
Messages
3
Hi folks

Every year I have to generate a list of 4 digit safe codes, for 60 sites, that change monthly. The safe codes cannot have repeating characters.

So I have 60 rows, 12 columns. Each cell must have a code between 0123 and 9876. They need to be randomly generated. It doesn't have to be unique - but the way the safes work is that all 4 numbers within any given code must be different. ie. 1234 is fine 1134 is not.

I've searched high and low for how to do this, and there are lots of threads with advice on how to generate random 4 digit numbers, but none that help with the character repetition. Last year I generated random numbers then manually changed any with duplicate characters. I don't want to do that again this year!

Can anyone help? I can follow instructions but I'm essentially a trained monkey when it comes to VBA code and complicated excel formulas so assume incompetence.

Thanks in advance for any help.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Hi, welcome to the board.

This sounds like a fun challenge !

There are probably lots of ways of doing this.

One way, you might call it a brute force solution, is to create a list of the acceptable numbers - there is clearly a definite number of acceptable codes which is some way less than 10,000.
You could probably generate a formula to help you create such a list.
Then use Excel's RAND function to select a code from the list at random.

In the meantime, I'll have a look if I can think of a way to write a formula that will generate such codes.
Some of the formula experts on this board can probably do that.
 
Upvote 0
Hi there. This code will do it for you (The shuffle routine is courtesy of Chip Pearson).
Code:
Sub randomiser()
Dim whitecard() As String

    choosefrom = Split("0,1,2,3,4,5,6,7,8,9", ",")


For rownum = 2 To 61
For colnum = 2 To 13
ShuffleArrayInPlace choosefrom
Cells(rownum, colnum).Value = choosefrom(0) & choosefrom(1) & choosefrom(2) & choosefrom(3)
Next colnum
Next rownum



End Sub
Sub ShuffleArrayInPlace(InArray As Variant)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ShuffleArrayInPlace
' This shuffles InArray to random order, randomized in place.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim N As Long
    Dim Temp As Variant
    Dim J As Long
   
    Randomize
    For N = LBound(InArray) To UBound(InArray)
        J = CLng(((UBound(InArray) - N) * Rnd) + N)
        If N <> J Then
            Temp = InArray(N)
            InArray(N) = InArray(J)
            InArray(J) = Temp
        End If
    Next N
End Sub

Just create a vba module, copy the above into it and then run the randomiser macro.
 
Last edited:
Upvote 0
Try this function. Put it in a code module in the File
Code:
Function RandDigits(Optional LenDigits As Integer = 4, Optional AllUniq As Boolean = True)    If LenDigits > 10 Then AllUniq = False
    Dim str1 As String, rDigit As String
    Dim i As Integer
    str1 = ""
    For i = 1 To LenDigits
        rDigit = Int((10 * Rnd))
        If AllUniq Then
            Do While InStr(1, str1, rDigit) > 0
                rDigit = Int((10 * Rnd))
            Loop
        End If
        str1 = str1 & rDigit
    Next i
    RandDigits = str1
End Function
Then simply put it as formula in a cell and fill down: =RandDigits()
 
Upvote 0
Here's more on the brute force formula solution.

In A1
=TEXT(1,"0000")

In A2
=TEXT(A1+1,"0000")
And copy down to row 9999.

In B1
=IF(OR(LEN(SUBSTITUTE(A1,LEFT(A1,1),""))<3,LEN(SUBSTITUTE(A1,MID(A1,2,1),""))<3,LEN(SUBSTITUTE(A1,MID(A1,3,1),""))<3),"invalid","ok")
And copy down to row 9999.

Then apply data filter, to select only the "ok" values, and use the RAND function to select a value from that list.

There are probably better ways of doing this . . .
 
Upvote 0
Hi there. This code will do it for you (The shuffle routine is courtesy of Chip Pearson).
Code:
Sub randomiser()
Dim whitecard() As String

    choosefrom = Split("0,1,2,3,4,5,6,7,8,9", ",")


For rownum = 2 To 61
For colnum = 2 To 13
ShuffleArrayInPlace choosefrom
Cells(rownum, colnum).Value = choosefrom(0) & choosefrom(1) & choosefrom(2) & choosefrom(3)
Next colnum
Next rownum



End Sub
Sub ShuffleArrayInPlace(InArray As Variant)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ShuffleArrayInPlace
' This shuffles InArray to random order, randomized in place.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim N As Long
    Dim Temp As Variant
    Dim J As Long
   
    Randomize
    For N = LBound(InArray) To UBound(InArray)
        J = CLng(((UBound(InArray) - N) * Rnd) + N)
        If N <> J Then
            Temp = InArray(N)
            InArray(N) = InArray(J)
            InArray(J) = Temp
        End If
    Next N
End Sub

Just create a vba module, copy the above into it and then run the randomiser macro.

Looks like this works perfectly, thanks!
 
Upvote 0
Yes, absolutely. They just can't have repeating characters within the same code.

Thanks for answer,
so with reversed : 5040 codes, without reversed: 210 only

but you got solution
so
Have a nice day :)
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top