Selecting random winners - without doubles

[Thomm]

Hi guys,

It would be great if you could help. I need a formula to run a draw / sweepstakes.

Let's say there is a list of 500 names in the column B (numbers 1-500 in the column A) and I need to select 20 names at random without any doubles.

So far I only managed to come up with the formula:

=VLOOKUP(INT(RAND() * MAX(Records!A:A)),Records!A1:B$500, 2, FALSE)

It needs to be entered with F9 instead of Enter so that it does not change once I copy the formula to another cell.

Unfortunately this does not take care of the doubles. Once one of 500 records is selected it has to be excluded from the next round. How do I go about this?

Any help appreciated,

Tom

 

[shg]

In a convenient column, enter =RAND() and copy down. Sort everything by that column and take the top 20 (or whatever) names.

 

[Thomm]

Thanks shg.
This is certainly a possibility. It is simple and clean.
However I need to draw the winners one by one, in public (small but still in public) hence without showing the source data.
So I would have my list of names in one workbook and the formula in another workbook. I would paste a formula in a convenient cell and hit enter - or F9 - or run a macro etc and the winner 1 name would appear.
I would then move to another cell and do the same to draw another winner (Please note the winner 1 should not change at this or any other point in time).
Can you see any solution for such scenario in Excel?

 

[shg]

See https://www.box.com/s/8pk9rtb0ca4opycr9sfk

Excel Options > Formulas, tick Enable Interative Calculation and set Maximum Iterations to 1.

Enter X in C2 to reset, then clear C2, then press F9 to see winners sequentially.

Insert additional names as desired. The Winners row must be below the table of names (which you can hide).

 

[Thomm]

That's brilliant. Thank you very much shg.

See https://www.box.com/s/8pk9rtb0ca4opycr9sfk

Excel Options > Formulas, tick Enable Interative Calculation and set Maximum Iterations to 1.

Enter X in C2 to reset, then clear C2, then press F9 to see winners sequentially.

Insert additional names as desired. The Winners row must be below the table of names (which you can hide).

 

[shg]

You're welcome, good luck.