Randomize rows

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.

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


"Robb Quirk" <[email protected]> wrote in message
news:[email protected]...
>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.

Thanks for the help Bernie. Three questions:
1) Is there a way to define the range of cells that the function randomizes?
The data is grouped in rows 6 through 20, in columns B, C, and D.
2) Instead of popping up a window with a random set of data, is there a way
to re-write over the list of data with a randomized list?
3) If there are blank rows within the list, is there a way to not include
them?

Thanks again in advance.
Robb


"Bernie Deitrick" wrote:

> 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
>
>
> "Robb Quirk" <[email protected]> wrote in message
> news:[email protected]...
> >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.
>
>
>

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


"Robb Quirk" <[email protected]> wrote in message
news:[email protected]...
> Thanks for the help Bernie. Three questions:
> 1) Is there a way to define the range of cells that the function randomizes?
> The data is grouped in rows 6 through 20, in columns B, C, and D.
> 2) Instead of popping up a window with a random set of data, is there a way
> to re-write over the list of data with a randomized list?
> 3) If there are blank rows within the list, is there a way to not include
> them?
>
> Thanks again in advance.
> Robb
>
>
> "Bernie Deitrick" wrote:
>
>> 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
>>
>>
>> "Robb Quirk" <[email protected]> wrote in message
>> news:[email protected]...
>> >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.
>>
>>
>>

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

"Bernie Deitrick" wrote:

> 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
>
>
> "Robb Quirk" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks for the help Bernie. Three questions:
> > 1) Is there a way to define the range of cells that the function randomizes?
> > The data is grouped in rows 6 through 20, in columns B, C, and D.
> > 2) Instead of popping up a window with a random set of data, is there a way
> > to re-write over the list of data with a randomized list?
> > 3) If there are blank rows within the list, is there a way to not include
> > them?
> >
> > Thanks again in advance.
> > Robb
> >
> >
> > "Bernie Deitrick" wrote:
> >
> >> 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
> >>
> >>
> >> "Robb Quirk" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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.
> >>
> >>
> >>
>
>
>