Random selections

alexv

New Member
Joined
Jun 26, 2006
Messages
26
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?
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
Hi alexv

Please try:

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

Hope this helps
PGC
 

alexv

New Member
Joined
Jun 26, 2006
Messages
26
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
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

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,955
Messages
5,545,162
Members
410,667
Latest member
Gaexel
Top