RAND function without duplicates?

[JAgger1]

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

 

[Gord Dibben]

See instructions at John McGimpsey's site.

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

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


Gord

 

[Mike S]

See instructions at John McGimpsey's site.

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

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


Gord

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.

 

[JAgger1]

Excellent, thanks