Generate random number between a predefined set of values without duplicate numbers

[Pramodpandit123]

How can i generate random numbers of certain string length with predefined set of characters and numbers ?
For eg: Generate a 20 random numbers that only contains " 1 0 P K L @ " , of which string length = 18.
Output1= 1P0PL@P01K1PK@L0P
Output2= 1K11P@P0K@L0PP0PL
. . . . and so on.

 

[Rick Rothstein]

Where is the output to go... a range of cells (if so, starting where), a text file (if so, stored where), a VBA array (one or two dimensional) or a VBA delimited text string (what delimiter)?

 

[Pramodpandit123]

Where is the output to go... a range of cells (if so, starting where), a text file (if so, stored where), a VBA array (one or two dimensional) or a VBA delimited text string (what delimiter)?

Sorry for not being detailed on the question. Output can be done on range of cells suppose starting from A1.
I've tried the function but it may create duplicate if set of values is taken small. I just wanted to know if there is any other approach of solving this without creating duplicates.

Function RandomString(Length As Long) As Variant
Dim X As Long
For X = 1 To Length
RandomString = RandomString & [MID("01PLK@",RANDBETWEEN(1,6),1)]
Next
End Function

 

[joeu2004]

You might consider the VBA function below (enter it into a "normal" VBA module). Examples of usage:

Book1
	A	B	C
1	@P1PLK0P@L1@PPL0L0
2	0@010001111@K1@0@@		c
3	PLP1K1LL0KK0001L@L		e
4	L1P1PLP0L@LLKK1@LP		f
5	@0K1LKKL1K10@@000P		i
6	0P10L0@@11K0P@@LP0		j
7	1@1K@P0P0PLK1@@@PP		b
8	P@P@0100KLP1LP1K10		d
9	001PKP0K01L@L1L@KK		a
10	LK1PLL1@PKK@PKLLLL		h
11			g
12			#N/A
Sheet1

Forumulas:
A1: =randstring(18, "10PKL@")
A2: =randstring(18, "10PKL@", $A$1:A1)
C2: =randstring(1, "abcdefghij", $C$1:C1)
Copy A2 and C2 down their respective columns

randString returns #N/A in C12 because we cannot have more than 10 unique one-character strings.

However, with sufficiently long random strings, the likelihood of a duplicate is small.

Function randString(ByVal n As Long, ByVal s As String, Optional r As Variant) As Variant
Const nloop As Long = 20
Dim slen As Long, i As Long, loopcnt
Dim res As String

slen = Len(s)
If slen = 0 Or n <= 0 Then Exit Function
Randomize

again:

If loopcnt = nloop Then randString = CVErr(xlErrNA): Exit Function
loopcnt = loopcnt + 1
res = ""
For i = 1 To n
    res = res & Mid(s, Int(slen * Rnd) + 1, 1)
Next
If Not IsMissing(r) Then
    For i = 1 To r.Count
        If res = r(i) Then GoTo again
    Next
End If
randString = res
End Function

The variable "n" is the number of random chararcters required in the result.

The variable "s" is the string of characters to choose from.

The optional variable "r" is the range of previously-generated random strings.

The variable nloopcnt limits the number of attempts to find a unique string. The limit is arbitrary.

Normally, I do not like to loop for a unique string. But with sufficiently long results (n), the likelihood of a duplicate is small.

PS.... Avoid using WorksheetFunction.RandBetween. It causes the VBA function to be "volatile", even if we omit the statement Application.Volatile.

 

[Marc L]

A VBA demonstration as a beginner starter :​

Sub Demo1()
  Const L = 18, M = 20, N = "1 0 P K L @"
    Dim S$(), U%, R&, G$, C%, T$(1 To M, 0)
        S = Split(N)
        U = UBound(S) + 1
        Randomize
    For R = 1 To M
        Do
            G = "":  For C = 1 To L:  G = G & S(Fix(Rnd * U)):  Next
        Loop Until IsError(Application.Match(G, T, 0))
            T(R, 0) = G
    Next
    With [A1].Resize(M)
        .Font.Name = "Courier New"
        .Value2 = T
        .Columns.AutoFit
    End With
End Sub