Randomizer

[excelnovicehouston]

Is there a way to use a randomizer for values not numbers - for example:

I have two columns
A________B
1 Dog______ Dog
2 Cat_____(blank)
3(blank)___ Wolf
4 Sheep___(blank)

I want a randomizer to only choose values for rows 1,2,4 in column A
or if I run the randomizer in column b to only choose values 1,3

is there a way to do this? preferably without a macro, but know that is likely not possible. Also, it is not be possible to sort column A or B and then run it.

Many thanks for the help.

 

[Sal Paradise]

The formula in A6 works (copy it in and hit the 'F9' key repeatedly to see it cycle through values randomly):

Excel Workbook
	A	B
1	Dog	Dog
2	Cat	*
3	*	Wolf
4	Sheep	*
5	*	*
6	Dog	Wolf
Sheet1

 

[excelnovicehouston]

Sal,
This works great. If you have a second, could you run me through the logic so I understand how it works?

Many thanks,
Novice

 

[Sal Paradise]

It will take more than a second, but here we go.

First type
=IF(A$1:A$4<>"",ROW(A$1:A$4))

And confirm with Ctrl-Shift-Enter

This is an array formula. It checks A1:A4, and if the cell isn't blank, it records the row.

Now click in the formula bar and hit F9

You should get:
={1;2;FALSE;4}

A1 has a value, so the array inserts '1', A2 has a value, so it adds '2', A3 is blank, so the IF statement is FALSE, and it adds 'FALSE' as a result. A4 has a value, so it adds '4'

So now we have an array with the rows with values.

We want to pick one of these at random, so we have to find a way of selecting a random row with a value from that array. The first step is to generate a random number. Type this into another cell:

=RANDBETWEEN(1,COUNT(IF(A$1:A$4<>"",ROW(A$1:A$4))))

Confirm with Ctrl-Shift-Enter again.

Now we run into the formula we first went over (in blue), only this time it's wrapped in a COUNT() function. What the will do is to count the amount of elements in the array. In this case, there are 3. So we can pick a random number between 1 and 3.

But we have to translate that 1-3 into one of the elements in the array. Enter this formula:
=SMALL(IF(A$1:A$4<>"",ROW(A$1:A$4)),RANDBETWEEN(1,COUNT(IF(A$1:A$4<>"",ROW(A$1:A$4)))))

Again, CSE to confirm.

What we're doing now is saying we want the Nth smallest number from the original array. The N is determined randomly, and because we limit the random number to the amount of elements in the array, we won't run into any problems as long as the array isn't blank. If we keep hitting 'F9' to refresh, we'll see the number isn't random between 1-3, but rather it's randomly selecting 1, 2 or 4.

So now we have to attach that to an original value. 1 2 and 4 are the row numbers of the values, so we can use the INDEX() function to select a random row 1 2 or 4 and return whatever is in that cell. Ergo:
=INDEX(A$1:A$4,SMALL(IF(A$1:A$4<>"",ROW(A$1:A$4)),RANDBETWEEN(1,COUNT(IF(A$1:A$4<>"",ROW(A$1:A$4))))),)

And that's how it works.

Make sense?

 

[excelnovicehouston]

brilliant stuff. i get it.

 

[Scott Krause]

I would like a formula that would put names into cells from B2 to B44 randomly. I have the names entered on a worksheet called Blank, if that helps. Thanks for your help