Column picked randomly with probability relative to number of entr

Hi! I am adapting a hangman game for my literacy students. The wordbank is
stored in a different sheet to the game, arranged in columns by the length of
the words. So for example, 5-letter words would be stored in column A,
6-letter ones in column B etc… The students can either choose how many
letters there are in the word, or this can be randomly chosen. If so, a
column will be randomly selected, and a word is then randomly selected from
this column. Selecting the word is not a problem, but I am having difficulty
randomly selecting a column, because though random, I want the probabilities
of the columns being chosen to be relative to the number of entries in it,
i.e. I want the random process for selecting a column to be weighted.

I could have simply selected a column at random, with each column having an
equal chance of being picked:
=INT(([Number of columns]*RAND())+1)
However, this means that words appearing in a column with very few entries
will occur far more often than words in a column ten times the size, for
example.

The probabilities for each column being picked need to be weighted, as in
the following example:
The percentage shows the percentage of the whole wordbank that appears in
that column, the decimals represent the probability with which I’d like that
column to be randomly chosen.

Entries in Column A 1 10% 0.1
Entries in Column B 2 20% 0.2
Entries in Column C 3 30% 0.3
Entries in Column D 4 40% 0.4

TOTAL 10 100% 1

So for example, I’d like column A to be picked at random 10% of the time,
column B 20% etc…

Does anybody know how to weight the RAND function in this way please? I’d be
really grateful for any help.
Many thanks in advance, Neil Goldwasser

First, John Walkenbach has a very nice Hangman game at:
http://www.j-walk.com/ss/excel/files/general.htm
(look for Hangman)

But have you thought about just building one giant array from all the words and
then randomly choosing a word from that giant array.

It seems to me that this would be equivalent and easier to code.

Option Explicit
Sub testme()

Dim myArr() As String
Dim iCtr As Long
Dim iCol As Long
Dim iRow As Long
Dim FirstRow As Long
Dim FirstCol As Long
Dim LastRow As Long
Dim LastCol As Long
Dim wks As Worksheet
Dim myChosenValue As Long
Dim myChosenWord As String

Set wks = Worksheets("sheet1")
With wks
FirstRow = 1
FirstCol = 1

LastCol = .Cells(FirstRow, .Columns.Count).End(xlToLeft).Column
iCtr = 0
For iCol = FirstCol To LastCol
For iRow = FirstRow To .Cells(.Rows.Count, iCol).End(xlUp).Row
If .Cells(iRow, iCol).Value = "" Then
'do nothing
Else
iCtr = iCtr + 1
ReDim Preserve myArr(1 To iCtr)
myArr(iCtr) = .Cells(iRow, iCol).Value
End If
Next iRow
Next iCol
End With

If iCtr > 0 Then
Randomize
myChosenValue _
= Int((UBound(myArr) - LBound(myArr) + 1) * Rnd + LBound(myArr))
myChosenWord = myArr(myChosenValue)
MsgBox myChosenWord
Else
MsgBox "no words!"
End If

End Sub


Neil Goldwasser wrote:
>
> Hi! I am adapting a hangman game for my literacy students. The wordbank is
> stored in a different sheet to the game, arranged in columns by the length of
> the words. So for example, 5-letter words would be stored in column A,
> 6-letter ones in column B etc… The students can either choose how many
> letters there are in the word, or this can be randomly chosen. If so, a
> column will be randomly selected, and a word is then randomly selected from
> this column. Selecting the word is not a problem, but I am having difficulty
> randomly selecting a column, because though random, I want the probabilities
> of the columns being chosen to be relative to the number of entries in it,
> i.e. I want the random process for selecting a column to be weighted.
>
> I could have simply selected a column at random, with each column having an
> equal chance of being picked:
> =INT(([Number of columns]*RAND())+1)
> However, this means that words appearing in a column with very few entries
> will occur far more often than words in a column ten times the size, for
> example.
>
> The probabilities for each column being picked need to be weighted, as in
> the following example:
> The percentage shows the percentage of the whole wordbank that appears in
> that column, the decimals represent the probability with which I’d like that
> column to be randomly chosen.
>
> Entries in Column A 1 10% 0.1
> Entries in Column B 2 20% 0.2
> Entries in Column C 3 30% 0.3
> Entries in Column D 4 40% 0.4
>
> TOTAL 10 100% 1
>
> So for example, I’d like column A to be picked at random 10% of the time,
> column B 20% etc…
>
> Does anybody know how to weight the RAND function in this way please? I’d be
> really grateful for any help.
> Many thanks in advance, Neil Goldwasser

--

Dave Peterson

Thanks Dave - I too liked John Walkenbach's game - and that is in fact the
one that I have been adapting! (with his permission) Because I work in
Learnng Support, I needed to alter the wordbank a bit to suit the level of my
students, and this involved adapting the code to allow for 5-letter words, as
well as some particular spellings that are 13-letters long etc...

John very kindly left the code open so I have been able to edit parts of it,
and it functions just fine, but I haven't been able to rejig the random
choosing of the column. In John's version, all the columns have 200 entries,
so each word has an exactly equal probability of coming up, but the wordbank
I intend to use would not be uniform. That's why I'd tried to weight the
random process, but I'm at a loss for ideas on how to.

Thank you very much for the code though. I apologise for not forgetting to
mention that it was John's version that I'm adapting, but thank you for your
help.

Any ideas on how to weight the probabilities?
Cheers, Neil



"Dave Peterson" wrote:

> First, John Walkenbach has a very nice Hangman game at:
> http://www.j-walk.com/ss/excel/files/general.htm
> (look for Hangman)
>
> But have you thought about just building one giant array from all the words and
> then randomly choosing a word from that giant array.
>
> It seems to me that this would be equivalent and easier to code.
>
> Option Explicit
> Sub testme()
>
> Dim myArr() As String
> Dim iCtr As Long
> Dim iCol As Long
> Dim iRow As Long
> Dim FirstRow As Long
> Dim FirstCol As Long
> Dim LastRow As Long
> Dim LastCol As Long
> Dim wks As Worksheet
> Dim myChosenValue As Long
> Dim myChosenWord As String
>
> Set wks = Worksheets("sheet1")
> With wks
> FirstRow = 1
> FirstCol = 1
>
> LastCol = .Cells(FirstRow, .Columns.Count).End(xlToLeft).Column
> iCtr = 0
> For iCol = FirstCol To LastCol
> For iRow = FirstRow To .Cells(.Rows.Count, iCol).End(xlUp).Row
> If .Cells(iRow, iCol).Value = "" Then
> 'do nothing
> Else
> iCtr = iCtr + 1
> ReDim Preserve myArr(1 To iCtr)
> myArr(iCtr) = .Cells(iRow, iCol).Value
> End If
> Next iRow
> Next iCol
> End With
>
> If iCtr > 0 Then
> Randomize
> myChosenValue _
> = Int((UBound(myArr) - LBound(myArr) + 1) * Rnd + LBound(myArr))
> myChosenWord = myArr(myChosenValue)
> MsgBox myChosenWord
> Else
> MsgBox "no words!"
> End If
>
> End Sub
>
>
> Neil Goldwasser wrote:
> >
> > Hi! I am adapting a hangman game for my literacy students. The wordbank is
> > stored in a different sheet to the game, arranged in columns by the length of
> > the words. So for example, 5-letter words would be stored in column A,
> > 6-letter ones in column B etc… The students can either choose how many
> > letters there are in the word, or this can be randomly chosen. If so, a
> > column will be randomly selected, and a word is then randomly selected from
> > this column. Selecting the word is not a problem, but I am having difficulty
> > randomly selecting a column, because though random, I want the probabilities
> > of the columns being chosen to be relative to the number of entries in it,
> > i.e. I want the random process for selecting a column to be weighted.
> >
> > I could have simply selected a column at random, with each column having an
> > equal chance of being picked:
> > =INT(([Number of columns]*RAND())+1)
> > However, this means that words appearing in a column with very few entries
> > will occur far more often than words in a column ten times the size, for
> > example.
> >
> > The probabilities for each column being picked need to be weighted, as in
> > the following example:
> > The percentage shows the percentage of the whole wordbank that appears in
> > that column, the decimals represent the probability with which I’d like that
> > column to be randomly chosen.
> >
> > Entries in Column A 1 10% 0.1
> > Entries in Column B 2 20% 0.2
> > Entries in Column C 3 30% 0.3
> > Entries in Column D 4 40% 0.4
> >
> > TOTAL 10 100% 1
> >
> > So for example, I’d like column A to be picked at random 10% of the time,
> > column B 20% etc…
> >
> > Does anybody know how to weight the RAND function in this way please? I’d be
> > really grateful for any help.
> > Many thanks in advance, Neil Goldwasser
>
> --
>
> Dave Peterson
>

Only the one I suggested--and that weights each word the same. But that seems
equivalent to me.

Neil Goldwasser wrote:
>
> Thanks Dave - I too liked John Walkenbach's game - and that is in fact the
> one that I have been adapting! (with his permission) Because I work in
> Learnng Support, I needed to alter the wordbank a bit to suit the level of my
> students, and this involved adapting the code to allow for 5-letter words, as
> well as some particular spellings that are 13-letters long etc...
>
> John very kindly left the code open so I have been able to edit parts of it,
> and it functions just fine, but I haven't been able to rejig the random
> choosing of the column. In John's version, all the columns have 200 entries,
> so each word has an exactly equal probability of coming up, but the wordbank
> I intend to use would not be uniform. That's why I'd tried to weight the
> random process, but I'm at a loss for ideas on how to.
>
> Thank you very much for the code though. I apologise for not forgetting to
> mention that it was John's version that I'm adapting, but thank you for your
> help.
>
> Any ideas on how to weight the probabilities?
> Cheers, Neil
>
> "Dave Peterson" wrote:
>
> > First, John Walkenbach has a very nice Hangman game at:
> > http://www.j-walk.com/ss/excel/files/general.htm
> > (look for Hangman)
> >
> > But have you thought about just building one giant array from all the words and
> > then randomly choosing a word from that giant array.
> >
> > It seems to me that this would be equivalent and easier to code.
> >
> > Option Explicit
> > Sub testme()
> >
> > Dim myArr() As String
> > Dim iCtr As Long
> > Dim iCol As Long
> > Dim iRow As Long
> > Dim FirstRow As Long
> > Dim FirstCol As Long
> > Dim LastRow As Long
> > Dim LastCol As Long
> > Dim wks As Worksheet
> > Dim myChosenValue As Long
> > Dim myChosenWord As String
> >
> > Set wks = Worksheets("sheet1")
> > With wks
> > FirstRow = 1
> > FirstCol = 1
> >
> > LastCol = .Cells(FirstRow, .Columns.Count).End(xlToLeft).Column
> > iCtr = 0
> > For iCol = FirstCol To LastCol
> > For iRow = FirstRow To .Cells(.Rows.Count, iCol).End(xlUp).Row
> > If .Cells(iRow, iCol).Value = "" Then
> > 'do nothing
> > Else
> > iCtr = iCtr + 1
> > ReDim Preserve myArr(1 To iCtr)
> > myArr(iCtr) = .Cells(iRow, iCol).Value
> > End If
> > Next iRow
> > Next iCol
> > End With
> >
> > If iCtr > 0 Then
> > Randomize
> > myChosenValue _
> > = Int((UBound(myArr) - LBound(myArr) + 1) * Rnd + LBound(myArr))
> > myChosenWord = myArr(myChosenValue)
> > MsgBox myChosenWord
> > Else
> > MsgBox "no words!"
> > End If
> >
> > End Sub
> >
> >
> > Neil Goldwasser wrote:
> > >
> > > Hi! I am adapting a hangman game for my literacy students. The wordbank is
> > > stored in a different sheet to the game, arranged in columns by the length of
> > > the words. So for example, 5-letter words would be stored in column A,
> > > 6-letter ones in column B etc… The students can either choose how many
> > > letters there are in the word, or this can be randomly chosen. If so, a
> > > column will be randomly selected, and a word is then randomly selected from
> > > this column. Selecting the word is not a problem, but I am having difficulty
> > > randomly selecting a column, because though random, I want the probabilities
> > > of the columns being chosen to be relative to the number of entries in it,
> > > i.e. I want the random process for selecting a column to be weighted.
> > >
> > > I could have simply selected a column at random, with each column having an
> > > equal chance of being picked:
> > > =INT(([Number of columns]*RAND())+1)
> > > However, this means that words appearing in a column with very few entries
> > > will occur far more often than words in a column ten times the size, for
> > > example.
> > >
> > > The probabilities for each column being picked need to be weighted, as in
> > > the following example:
> > > The percentage shows the percentage of the whole wordbank that appears in
> > > that column, the decimals represent the probability with which I’d like that
> > > column to be randomly chosen.
> > >
> > > Entries in Column A 1 10% 0.1
> > > Entries in Column B 2 20% 0.2
> > > Entries in Column C 3 30% 0.3
> > > Entries in Column D 4 40% 0.4
> > >
> > > TOTAL 10 100% 1
> > >
> > > So for example, I’d like column A to be picked at random 10% of the time,
> > > column B 20% etc…
> > >
> > > Does anybody know how to weight the RAND function in this way please? I’d be
> > > really grateful for any help.
> > > Many thanks in advance, Neil Goldwasser
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Firstly, my apologies for absent-mindedly forgetting to respond to this very
helpful post Dave. I posted the initial question during a particularly busy
period at work, and I'm afraid it got swallowed up with all the other things
I was working on - I'm very sorry!

For other people that may be browsing for similar solutions, there is also
another useful procedure code for forming one big array on the following post:
"Combine several columns of different length into one single column"

Many thanks again for your help Dave, Neil



"Dave Peterson" wrote:

> Only the one I suggested--and that weights each word the same. But that seems
> equivalent to me.
>
> Neil Goldwasser wrote:
> >
> > Thanks Dave - I too liked John Walkenbach's game - and that is in fact the
> > one that I have been adapting! (with his permission) Because I work in
> > Learnng Support, I needed to alter the wordbank a bit to suit the level of my
> > students, and this involved adapting the code to allow for 5-letter words, as
> > well as some particular spellings that are 13-letters long etc...
> >
> > John very kindly left the code open so I have been able to edit parts of it,
> > and it functions just fine, but I haven't been able to rejig the random
> > choosing of the column. In John's version, all the columns have 200 entries,
> > so each word has an exactly equal probability of coming up, but the wordbank
> > I intend to use would not be uniform. That's why I'd tried to weight the
> > random process, but I'm at a loss for ideas on how to.
> >
> > Thank you very much for the code though. I apologise for not forgetting to
> > mention that it was John's version that I'm adapting, but thank you for your
> > help.
> >
> > Any ideas on how to weight the probabilities?
> > Cheers, Neil
> >
> > "Dave Peterson" wrote:
> >
> > > First, John Walkenbach has a very nice Hangman game at:
> > > http://www.j-walk.com/ss/excel/files/general.htm
> > > (look for Hangman)
> > >
> > > But have you thought about just building one giant array from all the words and
> > > then randomly choosing a word from that giant array.
> > >
> > > It seems to me that this would be equivalent and easier to code.
> > >
> > > Option Explicit
> > > Sub testme()
> > >
> > > Dim myArr() As String
> > > Dim iCtr As Long
> > > Dim iCol As Long
> > > Dim iRow As Long
> > > Dim FirstRow As Long
> > > Dim FirstCol As Long
> > > Dim LastRow As Long
> > > Dim LastCol As Long
> > > Dim wks As Worksheet
> > > Dim myChosenValue As Long
> > > Dim myChosenWord As String
> > >
> > > Set wks = Worksheets("sheet1")
> > > With wks
> > > FirstRow = 1
> > > FirstCol = 1
> > >
> > > LastCol = .Cells(FirstRow, .Columns.Count).End(xlToLeft).Column
> > > iCtr = 0
> > > For iCol = FirstCol To LastCol
> > > For iRow = FirstRow To .Cells(.Rows.Count, iCol).End(xlUp).Row
> > > If .Cells(iRow, iCol).Value = "" Then
> > > 'do nothing
> > > Else
> > > iCtr = iCtr + 1
> > > ReDim Preserve myArr(1 To iCtr)
> > > myArr(iCtr) = .Cells(iRow, iCol).Value
> > > End If
> > > Next iRow
> > > Next iCol
> > > End With
> > >
> > > If iCtr > 0 Then
> > > Randomize
> > > myChosenValue _
> > > = Int((UBound(myArr) - LBound(myArr) + 1) * Rnd + LBound(myArr))
> > > myChosenWord = myArr(myChosenValue)
> > > MsgBox myChosenWord
> > > Else
> > > MsgBox "no words!"
> > > End If
> > >
> > > End Sub
> > >
> > >
> > > Neil Goldwasser wrote:
> > > >
> > > > Hi! I am adapting a hangman game for my literacy students. The wordbank is
> > > > stored in a different sheet to the game, arranged in columns by the length of
> > > > the words. So for example, 5-letter words would be stored in column A,
> > > > 6-letter ones in column B etc… The students can either choose how many
> > > > letters there are in the word, or this can be randomly chosen. If so, a
> > > > column will be randomly selected, and a word is then randomly selected from
> > > > this column. Selecting the word is not a problem, but I am having difficulty
> > > > randomly selecting a column, because though random, I want the probabilities
> > > > of the columns being chosen to be relative to the number of entries in it,
> > > > i.e. I want the random process for selecting a column to be weighted.
> > > >
> > > > I could have simply selected a column at random, with each column having an
> > > > equal chance of being picked:
> > > > =INT(([Number of columns]*RAND())+1)
> > > > However, this means that words appearing in a column with very few entries
> > > > will occur far more often than words in a column ten times the size, for
> > > > example.
> > > >
> > > > The probabilities for each column being picked need to be weighted, as in
> > > > the following example:
> > > > The percentage shows the percentage of the whole wordbank that appears in
> > > > that column, the decimals represent the probability with which I’d like that
> > > > column to be randomly chosen.
> > > >
> > > > Entries in Column A 1 10% 0.1
> > > > Entries in Column B 2 20% 0.2
> > > > Entries in Column C 3 30% 0.3
> > > > Entries in Column D 4 40% 0.4
> > > >
> > > > TOTAL 10 100% 1
> > > >
> > > > So for example, I’d like column A to be picked at random 10% of the time,
> > > > column B 20% etc…
> > > >
> > > > Does anybody know how to weight the RAND function in this way please? I’d be
> > > > really grateful for any help.
> > > > Many thanks in advance, Neil Goldwasser
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>