Random entry from list

[Job]

Anyone know how to insert a random entry into a cell that is drawn from a
list or range of cells? For example, if I have AA,AB,AC,AD,AE,AF in A1:A6
and in A7 I wanted it to give me randomly one of the values from the range.

Cheers,

Job

 

[Juan Pablo González]

Try this:

http://www.tushar-mehta.com/excel/newsgroups/rand_selection/index.html

 

[Guest]

Hi,
Get a random number between 1 and 6:
=INT(RAND()*6+1)
Get the element corresponding to this random number:
=INDEX(A1:A6,INT(RAND()*6+1))

The main issue is that the RAND function is volatile therefore it will
recompute evry time something changes in the sheet even though it has nothing
to do with A1:A7.
If you do not want the above behavior you could use you own vba function
NRAND similar to RAND but not volatile. In a code module:
Function NRAND()
Randomize
NRAND = Rnd()
End Function
Now you can use it in the sheet in the same way:
=INDEX(A1:A6,INT(NRAND()*6+1))

Regards,
sebastienm

 

[Guest]

sub rando()
m = int(rnd(1)*6)+1
range("a7").value = cells(1,m).value
end sub

 

[Job]

Ok Perfect...looks like all these will work. Now to add the next level of
complexity. What the end result of this will be is a grid. The list will
have in column b the number of times the the corresponding value in A will
show up. For example;

using the previous example; if A1 = AA and B1 = 2 then in the grid AA will
show up twice, but in random locations. The idea is that you have the above
mentioned list. Then you have a grid with cells. in each of the cells is a
formula or the vba code that will populate each of the cells with a random
value from the list, and put it in the grid the number of times
corresponding in column B. Hopefully that makes sense. I'm trying to
figure this out now, but if someone has an idea, any response is
appreciated.

 

[Guest]

What about making a custom type for your grid items:

Public Type GridItem
itemValue as String
numOccurences as Integer
End Type

GridItems (5) as GridItem 'will hold 6 grid items, indexed 0 to 5

if you have your values in column A, rows 1 to 6 and your frequency numbers
in column B, rows 1 to 6, you could populate your array of items with

For rowNum = 1 to 6

GridItems(rowNum - 1).ItemValue = Cells(rowNum, 1).value
GridItems(rowNum -1).numOccurences = Cells(rowNum,2).value

Next rowNum


If your grid will not be densely populated you can just ignore collisions
and generate random row and column numbers:

if targetCell.value <> "" then
'skip cell


Start with the first item in your GridItems array and decrement the
numOccurences "property" each time you successfully place an item.. When it
hits zero, increment your GridItems() index and place the next item in the
grid.

If the grid is densely populated it could potentially take a long time to
fill the grid if you ignore collisions completely. To reduce/avoid
collision, you could periodically poll all cells in the grid range. Add the
address of any empty cells to an array of grid targets and generate a random
index. Periodically update your array of empty cells to reduce collisions
(or use a more complext array type that will let you easily remove items from
the list ). For example, you can track the number of consecutive collisions.
If you get 3 or 4 collisions in a row, call your FindTargets function to
update (and return) a new list. Use UBound(gridTargets) as the upper bound
of your random number generator.