Randomize rows

R

Robb Quirk

I need to be able to randomize a set of data upon clicking a button. Each
set of data consists of three or four cells in one row, and can have numbers
or letters in it. The purpose of this is similar to pulling a name out of a
hat for a raffle drawing. Any ideas? Thanks in advance.
 
B

Bernie Deitrick

Robb,

You could randomly pick the data and display it using a macro. Assign the macro below to your
button. The macro was written assuming your data table of values (not formulas) starts in cell A1,
with headers in row 1, and no blank rows within your table.

HTH,
Bernie
MS Excel MVP

Sub ShowRandomValue()
Dim myRow As Integer
Dim myCount As Integer
Dim i As Integer
Dim myStr As String

myStr = ""
myCount = Range("A:A").SpecialCells(xlCellTypeConstants).Cells.Count - 1

Randomize
myRow = Application.RoundUp(Rnd() * myCount, 0) + 1
For i = 1 To Range("A1").CurrentRegion.Columns.Count
myStr = myStr & " " & Cells(myRow, i).Value
Next i

MsgBox myStr

End Sub
 
B

Bernie Deitrick

Robb,

You could use a fourth column with the function

=RAND()

copied to match your data table. Then press F9, and re-sort your entire table based on the fourth
column to get a new, randomized set.

Or you could use a set of four formulas to pull a random value from your table. For example, use
this formula in cell F1:

=RANDBETWEEN(ROW(B6),ROW(B20))

And then use these three formulas to pull random values from your table:

=INDEX(B:B,$F$1)
=INDEX(C:C,$F$1)
=INDEX(D:D,$F$1)

Each press of F9 (re-calc) will give you a new random number.

As for the blanks - you can work around it (with some more complex formulas), but it is far better
to design your data table to not include them.....

HTH,
Bernie
MS Excel MVP
 
W

WSI

Hi Bernie -
On this same idea, is there a way I could run a Macro that would randomly
choose 3 names from a list and display them in 3 cells?
So the output would look like
Winner 1 |Huey
Winner 2 |Dewey
Winner 3 |Louie
With Huey, Dewey and Louie being selected from the list in column A?
I am familiar with Macros, however rusty and have not worked with them for
awhile.
Appreciate any help -
Shane
 
Top