random letter generation

[Ann]

i'm trying to do a random letter generation, can this be done with a formula?
i need 3 random letters, which don't repeat and cannot include vowels - so
only 21 letters. or, can i use the random number generator somehow?
tia

 

[Bernie Deitrick]

Ann,

In cells A1:A21 enter your letters B - Z.
In cells B1:B21 enter the formula
=RAND()
In cell C1, enter the formula
=INDEX($A$1:$A$21,MATCH(LARGE($B$1:$B$21,ROW(A1)),$B$1:$B$21,FALSE))
and copy down to C2:C3.

Press F9 to get a new batch of letters...

Bernie
MS Excel MVP

 

[Ann]

thanks! is there an easy way to get the 3 letters in 1 cell?

 

[Ann]

nevermind i got it. thanks

thanks! is there an easy way to get the 3 letters in 1 cell?

 

[Bernie Deitrick]

nevermind i got it. thanks

Good. And you're quite welcome.

Bernie

 

[Rick Rothstein \(MVP - VB\)]

I'm not sure how you plan to use the generated letter triplets, but the
RAND() function that generates random numbers for use in a worksheet formula
will generate new values when anything is done to the worksheet that forces
a recalculation, so any letter triplets generated as a result of its use
will not be fixed and unchanging. Your best bet if you want fixed,
unchanging letter triplets is to use a macro. Right-click the worksheet tab
you want this functionality on and select View Code from the popup menu that
appears, then copy/paste the following code into the code window that
appeared...

Sub ThreeRandomLetters()
Dim X As Long
Dim Letter As String
Dim Triplet As String
Dim AvailableLetters As String
Randomize
AvailableLetters = "BCDFGHJKLMNPQRSTVWXYZ"
For X = 1 To 3
Letter = Mid(AvailableLetters, Int(Len(AvailableLetters) * Rnd + 1), 1)
Triplet = Triplet & Letter
AvailableLetters = Replace(AvailableLetters, Letter, "")
Next
ActiveCell.Value = Triplet
End Sub

Okay, now go back to the worksheet, press Alt+F8, select the
ThreeRandomLetters macro from the list and run it. Each time you do that, a
random, non-repeating letter triplet will be placed in whatever cell is
active at the time.

Rick