Random pick of a cell

  • Thread starter saman110 via OfficeKB.com
  • 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.
 
S

saman110 via OfficeKB.com

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
 
M

Max

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 ..
 
S

saman110 via OfficeKB.com

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.
 
M

Max

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top