Random selections

[alexv]

I have a data in an excel sheet from column A to column L that is 10 rows long. I want to randomly pick data from 20 cells. I have searched the postings with not much luck. Any help?

 

[pgc01]

Hi alexv

Please try:

=INDEX($A$1:$L$10,1+INT(RAND()*10),1+INT(RAND()*12))

Hope this helps
PGC

 

[alexv]

Thank you.

I also would like for it to ignore the empty cells in the table so that my random sample will not include any blank values. Is there a way to do that?

 

[pgc01]

Hi alexv

This is one possible solution.
This code writes in N1:N20 20 numbers taken randomly out of the non-empty cells in A1:L10

You can assign it to a Command Button to generate a different list each time you click on it.

Hope this helps
PGC

Sub PickRandNumbers()
Dim rCell As Range, dNumber(1 To 120) As Double, iMax As Integer, i As Integer

For Each rCell In Range("A1:L10")
    If rCell <> "" Then
        iMax = iMax + 1
        dNumber(iMax) = rCell
    End If
Next

For i = 1 To 20
    Range("N" & i) = dNumber(1 + Int(Rnd() * iMax))
Next
End Sub