Random pick of a cell

  • Thread starter Thread starter saman110 via OfficeKB.com
  • Start date Start date
S

saman110 via OfficeKB.com

Hello all,

I have many cells with text and numbers mixed (S2353, sam233,...). I would
like to get a non repeating random pick of a range without starting from
begining when I re open excel.

Thanks.
 
I got this code if its any good. It does start the sequence from begining
when you re open excel.


Public Function SampleNR(rSource As Range) As Variant
Dim vTemp As Variant
Dim nArr() As Long
Dim nSource As Long
Dim nDest As Long
Dim nRnd As Long
Dim nTemp As Long
Dim i As Long
Dim j As Long

Application.Volatile
nSource = rSource.Count
With Application.Caller
ReDim vTemp(1 To .Rows.Count, 1 To .Columns.Count)
nDest = .Count
End With
If nDest > nSource Then
SampleNR = CVErr(xlErrNA)
Else
ReDim nArr(1 To nSource)
For i = 1 To nSource
nArr(i) = i
Next i
For i = 1 To nDest
nRnd = Int(Rnd() * (nSource - i + 1)) + i
nTemp = nArr(nRnd)
nArr(nRnd) = nArr(i)
nArr(i) = nTemp
Next i
nTemp = 1
For i = 1 To UBound(vTemp, 1)
For j = 1 To UBound(vTemp, 2)
vTemp(i, j) = rSource(nArr(nTemp))
nTemp = nTemp + 1
Next j
Next i
SampleNR = vTemp
End If
End Function
 
Here's one way ..

Assume source data within D2:D4
Put in E2: =RAND()
Copy down to E4

Then just place in say, B2:
=INDEX(D$2:D$4,RANK(E2,E$2:E$4))

B2 will return a random pick of the source data within D2:D4
Adapt to suit ..
 
Hi thank you for your post. But what I wanted was to get One pick of random
numbers without dups. If that is possible please let me know.

Thanks.
 
Yes, think I understood your underlying intents, although it wasn't really
clear whether you wanted a single random pick from the source list items or
a randomized shuffle of the entire source list items.

The simple formulas suggestion works fine for me. It will return
non-repeating picks from the source list each time that the file is opened.

Here's a quick sample with 9 source list items to illustrate:
http://www.flypicture.com/download/OTI2NA==
Non-repeating random picks.xls
 
Back
Top