Pick a random number from a list then remove that number before picking next random number

[FreakyGirl]

Hello

I have an upcoming raffle that i need to distribute the tickets amoung the players equally.

For every 25% of the tickets there are 5 prizes to be won so a player has to have tickets in each range.

ie: 3253 tickets total. divided by 4 = 812.25 so for every 812 tickets theres 5 prizes.

last year some players had 20 tickets in 1 group & only 5 in another which is why its being changed for this year.

ie: PLAYER_A has 49 tickets total.

last years method:
10000-10999 group player had 17 tickets
11000-11999 group player had 12 tickets
12000-12999 group player had 16 tickets
13000-13253 group player had 4 tickets

this years method
10000-10812 group player had 13 tickets
10813-11625 group player had 12 tickets
11626-12438 group player had 12 tickets
12439-13253 group player had 12 tickets

i have already calculated how many tickets of each group a player needs

what i need help with is how would i assign the tickets in each group randomly to each player? nonsquential is prefered.

also after assigning the tickets to a player those tickets would have to be removed from the list so they arent available for the next player.


Each player will have a varying amount of tickets but never less than 7. no max number.

The total number of tickets is unknown but last year was 3,253 so im assuming it will be close to 4000 this year.

thanks in advance,
FreakyGirl

 

[Hui]

There is a formulaic solution for non-repeating random numbers at:
https://www.extendoffice.com/documents/excel/2484-excel-random-number-no-duplicates-no-repeat.html

 

[FreakyGirl]

There is a formulaic solution for non-repeating random numbers at:
https://www.extendoffice.com/documents/excel/2484-excel-random-number-no-duplicates-no-repeat.html

Hello.

Thank you for your quick response.

The solution you refered me to works but not the way i need. at least not yet.

their formula uses RAND() & has a start number of 1 & end number of 50.

when i change the 50 to anything higher than 1000 i get a #REF error. 1000 is ok. 1001 is not.

so i changed the RAND() to RANDBETWEEN(10000,13253) to see if it would help & i only get a #REF error.

how can i change their formula to get 1 column of random numbers without duplicates between the numbers 10000 & 13253?

thanks,
FreakyGirl

 

[p45cal]

You can get non-repeating random numbers:
Take one set, say from 10000 to10099. Put all these numbers in sequence in a column. In the adjacent column, add the fomula:
=RAND()
next to each number. Autofill will do this.
Then sort the two columns on the random column (no headers).
Now you can pick off your random numbers from the top down in sequence.
To get a new sequence of non-repeating numbers, you can recalculate the sheet and sort again.

 

[FreakyGirl]

You can get non-repeating random numbers:
Take one set, say from 10000 to10099. Put all these numbers in sequence in a column. In the adjacent column, add the fomula:
=RAND()
next to each number. Autofill will do this.
Then sort the two columns on the random column (no headers).
Now you can pick off your random numbers from the top down in sequence.
To get a new sequence of non-repeating numbers, you can recalculate the sheet and sort again.

Thanks for that suggestion! *slaps forehead* so simple!

now that i have the first group of numbers randomized, i want to list a players name x amount of times as dictated by the value in L12, L13, L14 etc.

the formula im using does correctly list the first players name 13 times but when it changes to the next name it doesnt change the cell reference so its always a FALSE statement.

In C15 where it changes the name i have:

=IF(COUNTIF(C$2:C14,J$12)<=L$12-1,J$12,OFFSET($J$12:$J$120,0+ROW()-14,0,1,1))

how can i get the formula to change the J & K references by 1 row once the COUNTIF from the prev name has been reached?

thanks in advance,
FreakyGirl

 

[p45cal]

In the attached,
a new column of numbers in column H being the cumulative total of column L. Put a zero in H11, and this formula in H12:
=SUM(L$12:L12)
pay attention to the dollar ($) symbols, then copy down.

In D2 (but really for C2):
=INDEX($J$12:$J$119,MATCH(ROW()-2,$H$11:$H$119))
copied down until the formula returns an error.
The -2 because the top formula is in row 2, so that in the topmost formula of this column, the ROW()-2 returns a zero. If the topmost formula for this column had been row 33, then the topmost formula would have read:
=INDEX($J$12:$J$119,MATCH(ROW()-33,$H$11:$H$119))

QED

 

[FreakyGirl]

In the attached,
a new column of numbers in column H being the cumulative total of column L. Put a zero in H11, and this formula in H12:
=SUM(L$12:L12)
pay attention to the dollar ($) symbols, then copy down.

In D2 (but really for C2):
=INDEX($J$12:$J$119,MATCH(ROW()-2,$H$11:$H$119))
copied down until the formula returns an error.
The -2 because the top formula is in row 2, so that in the topmost formula of this column, the ROW()-2 returns a zero. If the topmost formula for this column had been row 33, then the topmost formula would have read:
=INDEX($J$12:$J$119,MATCH(ROW()-33,$H$11:$H$119))

QED

This is GREAT! exactly what i needed.

sorry for delay in answering... with the holidays i havent had much time.

have a great new year!

FreakyGirl