Creating a random sequence of items from a weighted list with no duplicates

[stevesherrin]

I work for a playing card company and we often get calls for randomized expansion packs for games created from a pool of all the cards in the game. Some cards are more rare than others so should show up less times As an example, one game has a pool of 40 cards and I need to create 500 random 9 card sets but some cards are twice as likely to be drawn as others. I currently create a list of multiple copies of the filenames of each card, so each rare card appears once in my filename pool. The formula I use is :

=INDEX(filenames,(INDEX(UNIQUE(RANDARRAY(10^4, 1, 1, 217, TRUE)), SEQUENCE(9))))

This works well but there are 2 issues with it:

1) In some cases there are 4-5 rarity levels, some cards needing to appear 220 times in the filename list. The list is over 3000 filenames long. It is very time-consuming (and tedious) t put this list together.
2) While it minimizes duplicates within a single set, it doesn't completely get rid of them, especially with larger sets (36-54 random cards per set).

I have been trying to use a true weighted formula for this but the formulas I have found for weighted pulls from a list do not generate sequences (ie- a list of 9 cards)... They only produce one. The option that has been offered is putting the formula in one cell and dragging the handle down to duplicate the formula the 8 cells beneath it. While this works, there's no way to make the separate cards unique since they are drawn separately (since they are not a sequence created by a single formula in one cell that auto generates 9 entries in the column.

This new formula is:

=INDEX(filenames,MATCH(RAND(),cumulative))

filenames is a named range in the excel sheet (B3:B8) and cumulative is a named range (D3:D8)

weighted random.xlsx
	A	B	C	D	E	F
1		value	probability	cumulative		weighted random
2
3		ape.jpg	5%	0.00		frog.jpg
4		cat.jpg	10%	0.05
5		dog.jpg	10%	0.15
6		bear.jpg	20%	0.25
7		dolphin.jpg	25%	0.45
8		frog.jpg	30%	0.70
Sheet1

Cell Formulas
Range	Formula
F3	F3	=INDEX(filenames,MATCH(RAND(),cumulative))
D3:D8	D3	=SUM(D2,C2)

Named Ranges
Name	Refers To	Cells
cumulative	=Sheet1!$D$3:$D$8	F3, D4
filenames	=Sheet1!$B$3:$B$8	F3

Is there a way to modify this to create a sequence of random items from the list in a column and to prevent duplicates? Or maybe a VB script? Unfortunately I am a noob to excel and have no VB experience at all.

Thanks in advance for any suggestions you may have.

 

[HighAndWilder]

I work for a playing card company and we often get calls for randomized expansion packs for games created from a pool of all the cards in the game. Some cards are more rare than others so should show up less times As an example, one game has a pool of 40 cards and I need to create 500 random 9 card sets but some cards are twice as likely to be drawn as others. I currently create a list of multiple copies of the filenames of each card, so each rare card appears once in my filename pool. The formula I use is :

=INDEX(filenames,(INDEX(UNIQUE(RANDARRAY(10^4, 1, 1, 217, TRUE)), SEQUENCE(9))))

This works well but there are 2 issues with it:

1) In some cases there are 4-5 rarity levels, some cards needing to appear 220 times in the filename list. The list is over 3000 filenames long. It is very time-consuming (and tedious) t put this list together.
2) While it minimizes duplicates within a single set, it doesn't completely get rid of them, especially with larger sets (36-54 random cards per set).

I have been trying to use a true weighted formula for this but the formulas I have found for weighted pulls from a list do not generate sequences (ie- a list of 9 cards)... They only produce one. The option that has been offered is putting the formula in one cell and dragging the handle down to duplicate the formula the 8 cells beneath it. While this works, there's no way to make the separate cards unique since they are drawn separately (since they are not a sequence created by a single formula in one cell that auto generates 9 entries in the column.

This new formula is:

=INDEX(filenames,MATCH(RAND(),cumulative))

filenames is a named range in the excel sheet (B3:B8) and cumulative is a named range (D3:D8)

weighted random.xlsx
	A	B	C	D	E	F
1		value	probability	cumulative		weighted random
2
3		ape.jpg	5%	0.00		frog.jpg
4		cat.jpg	10%	0.05
5		dog.jpg	10%	0.15
6		bear.jpg	20%	0.25
7		dolphin.jpg	25%	0.45
8		frog.jpg	30%	0.70
Sheet1

Cell Formulas
Range	Formula
F3	F3	=INDEX(filenames,MATCH(RAND(),cumulative))
D3:D8	D3	=SUM(D2,C2)

Named Ranges
Name	Refers To	Cells
cumulative	=Sheet1!$D$3:$D$8	F3, D4
filenames	=Sheet1!$B$3:$B$8	F3

Is there a way to modify this to create a sequence of random items from the list in a column and to prevent duplicates? Or maybe a VB script? Unfortunately I am a noob to excel and have no VB experience at all.

Thanks in advance for any suggestions you may have.

How do you calculate the probability?

 

[shg]

This is a stars-and-bars combinatorics problem, with 9 stars (cards) partitioned into like names by 5 bars. So for example,

ape ape | cat | dog dog dog | bear bear bear | |

is a possibility, and so is

| cat | dog dog | bear bear bear | dolphin | frog frog

The five bars can appear in any combination of the 9+5 = 14 slots, so there are COMBIN(14, 5) = 2002 possible nine-card combinations, but not all are valid because you have limited numbers of each card (no ape ape ape | ... because there are only 2 ape cards in the original 40-card deck). That reduces it to 1299, and I think (still pondering) that those are equally likely. I can post a workbook that generates those combinations tomorrow if you're interested.

 

[stevesherrin]

This is a stars-and-bars combinatorics problem, with 9 stars (cards) partitioned into like names by 5 bars. So for example,

ape ape | cat | dog dog dog | bear bear bear | |

is a possibility, and so is

| cat | dog dog | bear bear bear | dolphin | frog frog

The five bars can appear in any combination of the 9+5 = 14 slots, so there are COMBIN(14, 5) = 2002 possible nine-card combinations, but not all are valid because you have limited numbers of each card (no ape ape ape | ... because there are only 2 ape cards in the original 40-card deck). That reduces it to 1299, and I think (still pondering) that those are equally likely. I can post a workbook that generates those combinations tomorrow if you're interested.

shg,

That may be a bit beyond me but I would be very interested in seeing what the solution looks like

Thanks for your help!

 

[stevesherrin]

How do you calculate the probability?

HighAndWilder,

The probability is actually provided by the client... for instance, all ultra-rare cards have a probability of 1, rare cards all have a probability of 10 (10 times as likely to be selected as ultra rare) and common a probability of 100 (100 times as likely as ultra-rare, 10 times as likely as rare).

 

[stevesherrin]

This is a stars-and-bars combinatorics problem, with 9 stars (cards) partitioned into like names by 5 bars. So for example,

ape ape | cat | dog dog dog | bear bear bear | |

is a possibility, and so is

| cat | dog dog | bear bear bear | dolphin | frog frog

The five bars can appear in any combination of the 9+5 = 14 slots, so there are COMBIN(14, 5) = 2002 possible nine-card combinations, but not all are valid because you have limited numbers of each card (no ape ape ape | ... because there are only 2 ape cards in the original 40-card deck). That reduces it to 1299, and I think (still pondering) that those are equally likely. I can post a workbook that generates those combinations tomorrow if you're interested.

shg,

One more thing based on your response... there is no issue with having multiple sets witht he same combination of cards, just not having any duplicates within one set

 

[shg]

>> there is no issue with having multiple sets with he same combination of cards, just not having any duplicates within one set

What does that mean?? What's OK, what isn't?