Randomly select from a range

[babsc01]

Howdy...this will probably be an easy one. I have a range of cells containing numbers. I want to randomly select a certain quantity of those numbers, non-repeating, and display the result in another range.

Any VBA or formula ideas? Thanks!

[Richie(UK)]

Hi b,

Tushar Mehta has some useful routines here:

http://www.tushar-mehta.com/excel/ne...ction/vba.html

[mvidas]

Hi babsc01,

I didn't actually look at Richie's link, so a similar thing may be there, but you could also use a sub like:

Sub LetsGiveItATry()
 Dim InputRange As Range, OutputRange As Range, CLL As Range
 Dim CellsColl As New Collection, i As Long, j As Long
Set InputRange = Range("A1:A35")
 Set OutputRange = Range("B1:B9")
If OutputRange.Cells.Count > InputRange.Cells.Count Then
  Msgbox "Output range bigger than input range. Please re-define."
  Exit Sub
 End If
 For Each CLL In InputRange.Cells
  CellsColl.Add CLL
 Next CLL
 Randomize
 For i = 1 To OutputRange.Cells.Count
  j = Int(Rnd() * CellsColl.Count + 1)
  OutputRange.Cells(i) = CellsColl(j)
  CellsColl.Remove j
 Next
End Sub

Just define the InputRange and the OutputRange, should take care of it all for you.
Matt

[babsc01]

This will work...thanks so much, Matt.

[mvidas]

Glad to hear it! You can mark it as Solved using "Thread Tools" right above your first message. That way everyone will know you're all set.

This thread must have been edited to remove the Randomize discussion. That's too bad, it was nice seeing people's views trying to figure out how excel works.

[babsc01]

I think it was deleted, too. Almost seemed like it was turning into a pretty heated discussion.

[mvidas]

My favorite kind

[Aaron Blood]

I think it was deleted, too. Almost seemed like it was turning into a pretty heated discussion.

...not heated. Too much caffeine in the afternoon I think.

The ending was cool though... Involved light sabers and acrobatics.

Some errant force lightning must've zapped a few of the innocently bystanding postings. It happens sometimes when the Jedi clash.