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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200707/1

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


saman110 wrote:
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200707/1

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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"saman110 via OfficeKB.com" <u35670@uwe wrote in message
news:7603ee25d4d25@uwe...
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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200707/1

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

well, if you want a full random shuffle of the source in D2:D4, just copy B2
down to B4.

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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.

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

well, if you want a full random shuffle of the source in D2:D4, just copy B2
down to B4.


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200708/1

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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"saman110 via OfficeKB.com" <u35670@uwe wrote in message
news:760d9387439a9@uwe...
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.