Create a string of 6 random constonants - and ensure that none of these values are in a specified cell

[ryanblything]

Hi,

I'd like to create a string of 6 random consonants (e.g., "kftryp") but with 2 caveats:

1. no letter repeats (e.g., you can't have "kftryk")
2. the string does not contain a consonant that is used in another specified cell (e.g., if the specified cell - A1 - contains "tiger" then "kftryp" is not suitable because it contains a "t" (so I need code that says "give me 6 unique consonants, and don't give me a consonant that is contained in e.g., cell A1).

I've figured out how to create a string of random consonants:

=MID("BCDFGHJKLMNPQRSTVWXYZ",RANDBETWEEN(1,21),1)&
=MID("BCDFGHJKLMNPQRSTVWXYZ",RANDBETWEEN(1,21),1)&
=MID("BCDFGHJKLMNPQRSTVWXYZ",RANDBETWEEN(1,21),1)&
=MID("BCDFGHJKLMNPQRSTVWXYZ",RANDBETWEEN(1,21),1)&
=MID("BCDFGHJKLMNPQRSTVWXYZ",RANDBETWEEN(1,21),1)&
=MID("BCDFGHJKLMNPQRSTVWXYZ",RANDBETWEEN(1,21),1)

but I don't know how to achieve the 2 caveats - any suggestions would be very much appreciated!
Thanks,
Ryan

 

[JoeMo]

Are you willing to consider a VBA solution? If yes, do all the letters have to be lower case as in your "kftryp" example? If not, is "kftryK" acceptable?

 

[JoeMo]

Getting close to bedtime so I'll assume you want to deal only with lower case consonants. Here's a user-defined function (UDF) you can try. Once installed, you can use it just like a worksheet function as in the example below where the Exclude range is cell A1 holding a value "tiger" and the consonant string contains 6 letters (the numChars argument). Below the example are the installation instructions and the UDF.

Excel Workbook
	A	B
1	tiger	fzdxnk
Sheet7

To install the UDF:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the UDF from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Use the UDF as you would any native Excel function.
7. Make sure you have enabled macros whenever you open the file or the code will not run.

Function RandLet(numChars As Integer, Exclude As Range) As String
'Assume want only lower case consonants
Dim d As Object
Dim Vowels As Variant
Dim Lets As String
Dim FinalResult As String
Application.Volatile
Set d = CreateObject("Scripting.dictionary")
For i = 1 To Len(Exclude.Value)
    x = d.Item(LCase(Mid(Exclude.Value, i, 1)))
Next i
'no vowels wanted
Vowels = Array("a", "e", "i", "o", "u")
For i = LBound(Vowels) To UBound(Vowels)
    x = d.Item(Vowels(i))
Next i
Do
    Lets = Chr(Int((26 * Rnd) + 97))  'generates a-z
    If Not d.exists(Lets) Then
        d.Add Lets, d.Count + 1
        FinalResult = FinalResult & Lets
        Lets = ""
    End If
Loop Until Len(FinalResult) >= numChars
RandLet = FinalResult
End Function

 

[footoo]

Here's an alternative function :

Function RandomString(Exclude As Range) As String
Dim a$, c%, x%, s$, rs$
a = "bcdfghjklmnpqrstvwxyz"
For c = 1 To Len(Exclude)
    a = Replace(a, Mid(LCase(Exclude), c, 1), "")
Next
For x = 1 To 6
    s = Mid(a, WorksheetFunction.RandBetween(1, Len(a)), 1)
    a = Replace(a, s, "")
    rs = rs & s
Next
RandomString = rs
End Function

 

[ryanblything]

Many thanks, Joe! That worked perfectly!
Ryan

 

[JoeMo]

Many thanks, Joe! That worked perfectly!
Ryan

You are welcome - thanks for the reply.

 

[footoo]

I think the code I posted is more efficient.