Solved: Randomly picked winners

[jackdandcoke]

I'm running a contest online that dumps entries into an excel sheet. I want to run a macro that randomly picks five winners from the list.

Originally I was going to generate a random number in column d using an if statement. =IF(ISNUMBER(C1),RANDBETWEEN(1,100),null).

What I would really like it to do is generate a random number in the d column if there is a value in the c column, then I would have my macro sort the numbers ascending and select the top 5 rows.

I'm not good with ranges in vba or excel and this is a static range so I'd really like the code to say


=if(isnumber(c1 'this of course has to apply to any cell in c' , RANDBETWEEN(1, 'here I would like it to count how many rows there are in c column' ),null)
My only problem really is how to get it to know what the range should be for the RANDBETWEEN and how to get it to not return anything instead of #NAME? if there is nothing in the c cell.

That is unless someone knows how to code it to pick 5 random lines and copy the contents of cells A,B, and C from those lines and paste them into another work sheet.

[Bob Phillips]

Try this


First, ensure cell D1 is empty and goto Tools>Options and on the Calculation
tab check the Iteration checkbox to stop the Circular Reference message.

Next, type this formula into cell D2
=IF(C2="","",IF(($D$1="")+(AND(D2>0,COUNTIF($D:$D,D2)=1)),D2,INT(RANDBETWEEN(1,COUNTA(C:C)))))
it should show a 0

Copy D2 down.

Finally, put some value in D1, say an 'x', and all the random numbers will
be generated, and they won't change.

To force a re-calculation, clear cell D1, edit cell D2, don't change it,
just edit to reset to 0, copy D2 down, and re-input an x in D1.

[jackdandcoke]

That solution worked! Thank you so much!