Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Randomize rows
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).Cel ls.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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Randomize rows
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).Cel ls.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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Randomize rows
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" wrote in message ... 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).Cel ls.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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Randomize rows
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" wrote in message ... 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).Cel ls.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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert rows based on specific value | Excel Worksheet Functions | |||
formula to use when number of rows changes dynamically | Excel Worksheet Functions | |||
Hiding Rows if the linked rows are blank | Excel Discussion (Misc queries) | |||
Row selections by row # OR by even/odd rows in another spreadsheet | Excel Discussion (Misc queries) | |||
Adding Rows to Master Sheet | New Users to Excel |