skipping a number if present in another column of numbers

[FreakyGirl]

Hello.

we do an annual online raffle in which a player can only win once. if a winners' ticket is drawn again, we toss it & draw another ticket.

it was pointed out to me that by removing the winners other tickets from the 'raffle box' it prevents duplicate winners. du-uh me.

however, its not that simple because of our system of 'drawing' the winners from the raffle box.

i can do it, with alot of array formulas (20 or so) but would rather have a better solution. my knowledge of excel is limited, & i usually get something working by going about it using unnecessary long formulas. the 1 array is slowing the sheet down, i cant imagine what 20 would do!

right now i have the worksheet bypassing previous winning tickets. works great. with each new draw, winning tickets are 'skipped'. but with each new draw the formula gets longer to accomidate for the new winning ticket. the worksheet is set up for 25 winners. the formula for the 25th winner is:

=IF(AND(CN3<>"",OR(CM3=$CP$11-1,CM3=$CP$12-1,CM3=$CP$13-1,CM3=$CP$14-1,CM3=$CP$15-1,CM3=$CP$16-1,CM3=$CP$17-1,CM3=$CP$18-1,CM3=$CP$19-1,CM3=$CP$20-1,CM3=$CP$21-1,CM3=$CP$22-1,CM3=$CP$23-1,CM3=$CP$24-1,CM3=$CP$25-1,CM3=$CP$26-1,CM3=$CP$27-1,CM3=$CP$28-1,CM3=$CP$29-1,CM3=$CP$30-1,CM3=$CP$31-1,CM3=$CP$32-1,CM3=$CP$33-1,CM3=$CP$34-1)),CM3+2,IF(CM3=$A$1,$B$1,CM3+1))

its a really long formula but i dont know how to get it to work any other way. i dread trying to come up with a formula to extract all the other tickets from the winners. there has to be an easier way.

for the first 5 winners from last year... that would be 227 tickets to remove alone!!

is there way to have a formula say if a number is present in this column then skip it? the column would prob be at least 1000 numbers.

thanks in advance,
FreakyGirl

 

[Hui]

Here is an alternative method

I'm note sure it is 100% random, as I suspect it favours the Ballots higher in the list, but sorting by the random number would fix that anyway

I will have a further play later

 

[FreakyGirl]

Here is an alternative method

I'm note sure it is 100% random, as I suspect it favours the Ballots higher in the list, but sorting by the random number would fix that anyway

I will have a further play later

Hello.

Thanks for your quick reply.

This is a great alternitive for picking tickets but i have to stay with what we are currently using.

The players like to watch the drawing as it happens & this way would not allow them to do that.

In addition, when playing with this i noticed that no ticket will be picked over 10099. i clicked on D3 at least 20x to be sure. The ranking is giving more than 1 of each of the Draw numbers and its only taking the first.

I need a way to extract those tickets that winners are still holding.

thanks for your time,
FreakyGirl