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

[be4con]

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.

 

[Gerald Higgins]

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.

 

[jmacleary]

Hi there. This code will do it for you (The shuffle routine is courtesy of Chip Pearson).

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.

 

[bobsan42]

Try this function. Put it in a code module in the File

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()

 

[Gerald Higgins]

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

 

[be4con]

Hi there. This code will do it for you (The shuffle routine is courtesy of Chip Pearson).

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!

 

[sandy666]

Out of curosity:
What about 1234 vs 4321 ? It is ok?

 

[be4con]

Out of curosity:
What about 1234 vs 4321 ? It is ok?

Yes, absolutely. They just can't have repeating characters within the same code.

 

[sandy666]

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