RAND function without duplicates?
 

I have a set of 20 numbers in cell A1:T1

I'm using =INDEX($A1:$T1,RAND()*COUNTA($A1:$T1)) in cell W1 to Z1 to
randomly select 4 numbers from my set. How would I modify my formula
so as to not get any duplicate's? Thanks

RAND function without duplicates?
 

See instructions at John McGimpsey's site.

http://www.mcgimpsey.com/excel/udfs/randint.html

Either Excel worksheet functions or VBA............your choice


Gord

On Mon, 18 Jun 2012 11:52:08 -0700 (PDT), JAgger1
wrote:

I have a set of 20 numbers in cell A1:T1

I'm using =INDEX($A1:$T1,RAND()*COUNTA($A1:$T1)) in cell W1 to Z1 to
randomly select 4 numbers from my set. How would I modify my formula
so as to not get any duplicate's? Thanks

RAND function without duplicates?
 

On 6/18/2012 12:29 PM, Gord Dibben wrote:
See instructions at John McGimpsey's site.

http://www.mcgimpsey.com/excel/udfs/randint.html

Either Excel worksheet functions or VBA............your choice


Gord

On Mon, 18 Jun 2012 11:52:08 -0700 (PDT), JAgger1
wrote:

I have a set of 20 numbers in cell A1:T1

I'm using =INDEX($A1:$T1,RAND()*COUNTA($A1:$T1)) in cell W1 to Z1 to
randomly select 4 numbers from my set. How would I modify my formula
so as to not get any duplicate's? Thanks

I have done this before using a listbox: fill the listbox with every
possible number, then randomly select an entry in the listbox and use
removeitem to remove it from the listbox so it can't be select twice.
You can get as many entries as the listbox holds in random order with no
possibility of duplicates, and the coding is very easy.

RAND function without duplicates?
 

Excellent, thanks