Return a random number from a list

[Guest]

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.

 

[Guest]

Assuming your numbers are in D1 - D8. Adjust as necessary

=INDEX(D18,RANDBETWEEN(1,COUNTA(D18)),1)

 

[Guest]

Thanks Mike,

I filled down, what about if i want each number to appear once only in my
new list, as described below?

Cheers.

 

[Guest]

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.

 

[Guest]

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