Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return a random number from a list
Hi,
if i have the following list on sheet1 1 2 3 4 5 6 7 8 i want to know how to return any random number from that list on another sheet. e.g. sheet2 2 4 5 1 3 6 8 7 and i want to the array to return them in a different order everytime the code is executed. can anyone help please? Many thanks, Bhupinder. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return a random number from a list
Assuming your numbers are in D1 - D8. Adjust as necessary
=INDEX(D1:D8,RANDBETWEEN(1,COUNTA(D1:D8)),1) "Bhupinder Rayat" wrote: Hi, if i have the following list on sheet1 1 2 3 4 5 6 7 8 i want to know how to return any random number from that list on another sheet. e.g. sheet2 2 4 5 1 3 6 8 7 and i want to the array to return them in a different order everytime the code is executed. can anyone help please? Many thanks, Bhupinder. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return a random number from a list
Thanks Mike,
I filled down, what about if i want each number to appear once only in my new list, as described below? Cheers. "Mike" wrote: Assuming your numbers are in D1 - D8. Adjust as necessary =INDEX(D1:D8,RANDBETWEEN(1,COUNTA(D1:D8)),1) "Bhupinder Rayat" wrote: Hi, if i have the following list on sheet1 1 2 3 4 5 6 7 8 i want to know how to return any random number from that list on another sheet. e.g. sheet2 2 4 5 1 3 6 8 7 and i want to the array to return them in a different order everytime the code is executed. can anyone help please? Many thanks, Bhupinder. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return a random number from a list
A bit more difficult but try this
Next to your list of numbers type RAND() and drag down. Sort the selection by the list of random numbers generated. The numbers 1 to 8 will also sort with no repeats. Copy this sorted selection to where you want it to be. "Bhupinder Rayat" wrote: Thanks Mike, I filled down, what about if i want each number to appear once only in my new list, as described below? Cheers. "Mike" wrote: Assuming your numbers are in D1 - D8. Adjust as necessary =INDEX(D1:D8,RANDBETWEEN(1,COUNTA(D1:D8)),1) "Bhupinder Rayat" wrote: Hi, if i have the following list on sheet1 1 2 3 4 5 6 7 8 i want to know how to return any random number from that list on another sheet. e.g. sheet2 2 4 5 1 3 6 8 7 and i want to the array to return them in a different order everytime the code is executed. can anyone help please? Many thanks, Bhupinder. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return a random number from a list
Here is some code that will do it:
As written, expects you numbers to start in A1 of sheet1 and go down the column. Writes the results to sheet2 in the same location. Change the code to match you data layout and requirements. Option Explicit Sub RandomizeRange() Dim rng As Range Dim cell As Range Dim i As Long With Worksheets("Sheet1") Set rng = .Range(.Cells(1, 1), _ .Cells(1, 1).End(xlDown)) End With Randomize Dim varr() As Variant ReDim varr(1 To rng.Count) Dim varr1 As Variant i = 0 For Each cell In rng i = i + 1 varr(i) = cell.Value Next varr1 = ShuffleArray(varr) For i = 1 To rng.Count With Worksheets("sheet2") .Cells(i, 1).Value = varr1(i) End With Next End Sub Public Function ShuffleArray(varr) ' ' Algorithm from: ' The Art of Computer Programming: _ ' SemiNumerical Algorithms Vol 2, 2nd Ed. ' Donald Knuth ' p. 139 ' ' Dim List() As Long Dim t As Long Dim i As Long Dim j As Long Dim k As Long Dim lngTemp As Long t = UBound(varr, 1) - LBound(varr, 1) + 1 ReDim List(1 To t) For i = 1 To t List(i) = varr(i) Next j = t Randomize For i = 1 To t k = Rnd() * j + 1 lngTemp = List(j) List(j) = List(k) List(k) = lngTemp j = j - 1 Next ShuffleArray = List End Function -- Regards, Tom Ogilvy "Bhupinder Rayat" wrote: Thanks Mike, I filled down, what about if i want each number to appear once only in my new list, as described below? Cheers. "Mike" wrote: Assuming your numbers are in D1 - D8. Adjust as necessary =INDEX(D1:D8,RANDBETWEEN(1,COUNTA(D1:D8)),1) "Bhupinder Rayat" wrote: Hi, if i have the following list on sheet1 1 2 3 4 5 6 7 8 i want to know how to return any random number from that list on another sheet. e.g. sheet2 2 4 5 1 3 6 8 7 and i want to the array to return them in a different order everytime the code is executed. can anyone help please? Many thanks, Bhupinder. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Random number from a list | Excel Discussion (Misc queries) | |||
generating random number list | Excel Worksheet Functions | |||
Generate random number from a list | Excel Worksheet Functions | |||
next logical number from list of about 2000 random numbers | Excel Programming | |||
How do I find random number in list of random alpha? (Position is. | Excel Discussion (Misc queries) |