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

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" wrote in message
...
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

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

"Bernie Deitrick" wrote:

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" wrote in message
...
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

nevermind i got it. thanks

"Ann" wrote:

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

"Bernie Deitrick" wrote:

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" wrote in message
...
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

nevermind i got it. thanks

Good. And you're quite welcome.

Bernie

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


"Ann" wrote in message
...
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