randomly select a word from a list

[kalim]

Hi excel users.
I have a list of words. What I need is for one of those words to be randomly selected and then placed into a used list so it can’t be used again. Therefore all words will eventually be selected only once randomly.
Lastly, would it be possible to reset the words back into their original list so the activity can be done as many times as needed.
Thanks.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 90px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>List one</TD><TD> </TD><TD>Used word list</TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>sat</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>bat</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>fun</TD><TD> </TD><TD> </TD><TD>can</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>red</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>went</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>and</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>I</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>two</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>to</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD>the</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD>can</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD>ran</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD>blue</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD>look</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD>sent</TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D4</TD><TD>=INDEX(A2:A16,RANDBETWEEN(1,COUNTA(A2:A16)))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[JamesW]

Hi there,

Quite an interesting challenge!

Not sure if this is exactly what you want though?

I was stuck a little on getting it to fill in the blanks, so I used i = i -1 which isn't the best way to do it, but it works.

Try this:

Sub randomWordSelection()
    lR = Range("A1").End(xlDown).Row

    cCheck = MsgBox("Do you want to clear the data first?", vbYesNo)
    
    If cCheck = vbYes Then
        Range("C2:C" & lR).ClearContents
    Else
        For i = 2 To lR
            rNum = Int((lR - 2 + 1) * Rnd + 2) 
            rWord = Cells(rNum, 1).Value 
            
            Set wFound = Range("C2:C" & lR).Find(rWord)
            
            If Not wFound Is Nothing Then
                i = i - 1
                If i = 1 Then Exit Sub
            End If
            
            If wFound Is Nothing Then Cells(i, 3).Value = rWord
        Next
    End If
End Sub

 

[Ruddles]

What a complete waste of time this is! Have none of you anything better to do with their time?

Okay, here's my effort: it needs column headings in cols A and C, and the way it's written it will extract the words to cell B2, so put "Selected Word" in B1 and highlight B2 in yellow.

Option Explicit
 
Public Sub ExtractRandomWord()
 
  Dim iLastRow As Long
  Dim iLastNew As Long
  Dim iPointer As Long
  Dim iInd As Long
  Dim iSwap As String
 
  iLastRow = Cells(Rows.Count, 1).End(xlUp).Row
  iLastNew = Cells(Rows.Count, 3).End(xlUp).Row
 
  If iLastRow > 1 Then
    iPointer = WorksheetFunction.RandBetween(2, iLastRow)
    iLastNew = iLastNew + 1
    Cells(iLastNew, 3) = Cells(iPointer, 1)
    Range("B2") = Cells(iPointer, 1)
    For iInd = iPointer To iLastRow + 1
      Cells(iInd, 1) = Cells(iInd + 1, 1)
    Next iInd
    iLastRow = iLastRow - 1
[COLOR=green]   ' If iLastRow = 1 Then ActiveSheet.Shapes("Button 1").TextFrame.Characters.Text = "Reset List"[/COLOR]
  Else
    Range("B2").ClearContents
    For iPointer = 2 To iLastNew
      For iInd = iPointer + 1 To iLastNew
        If Cells(iPointer, 3) > Cells(iInd, 3) Then
          iSwap = Cells(iPointer, 3)
          Cells(iPointer, 3) = Cells(iInd, 3)
          Cells(iInd, 3) = iSwap
        End If
      Next iInd
      Cells(iPointer, 1) = Cells(iPointer, 3)
      Cells(iPointer, 3).ClearContents
    Next iPointer
[COLOR=green]   ' ActiveSheet.Shapes("Button 1").TextFrame.Characters.Text = "Next Word"[/COLOR]
  End If
 
End Sub

Each time you run it, it extracts a random word from col A and adds it to col C, copying it to B2 in the process. When there are no more words left in col A, next time you run it it will sort the words out of col C and put them back in A.

If you create a command button called "Button 1" and assign it to ExtractRandomWord, you can uncomment the bits in green and this gives you a clicky which changes caption at the right moment.

 

[kalim]

Thanks so much for your replies JamesW and Ruddles.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
They both worked for me.<o></o>
<o></o>
JamesW: Thanks but that code does not do what I needed. I might need it in the future thou so thanks.<o></o>
<o></o>
Ruddles: Thanks. Your code does exactly what I needed. <o></o>
<o></o>
One last request thou to all.<o></o>
Is what Ruddles created in vba possible with just using formulas?<o></o>
<o> </o>
Thanks.

 

[Ruddles]

You can't move cells around using just formulae, so I'd say that was a complete show-stopper.

 

[kalim]

Hmm ok thanks anyway.