Weighted Random Item selection without replacement, drawn multiple times

Question

I'm trying to build a system where I have a list of Items with weights associated with them. I want to randomly pull items from this pool, using their weights to determine chance of selection.

In the example spreadsheet, I have 10 fruits. I want to pull 3 fruits from the pool. The same fruit cannot be pulled more than once. These 3 fruits would be a fruit-selection.

I want to do this many times, across multiple rows, each providing a new random fruit-selection.

1. I've come across a couple of issues:

• Occasionally I've got this error:

  [Error
  Function INDEX parameter 2 value is 8. Valid values are between 0 and 7 inclusive.]
  

• Sometimes the formula for pulling an item results in an empty cell.

2. I'm probably not also doing this the most efficient way. I may need up to 300 rows of these 'fruit-selections'
3. Finally, I would like to determine the number of fruits being drawn to make up each fruit-selection. I've not hooked this up yet as I was trying to get the above working.

I'm hoping there's an efficient way of doing this. Maybe using sequence?

Appreciate any help / advice on this one.

Spreadsheet is here

Answer 1

not sure if you realize it but this is contra-productive... you can either have weights or unique values - not both of them because after you assign weights to your items it means that some of them will appear more frequently than the other ones. example:

let's say you want to choose 2 random items from this list

weight     item
_______________
4          mango
2          papaya
3          jackfruit

so you actually want to choose from this probability:

mango
mango
mango
mango
papaya
papaya
jackfruit
jackfruit
jackfruit

now as soon as you introduce "no duplicates" rule you are limiting your choice only to unique items so your choice will be reduced to:

mango
papaya
jackfruit

and at this point, you just lost the whole idea with weights which are just pointless because choosing two random items from the above list will yeild only 3 possible outcomes so the probability (weight) of choosing each item is 1:1:1 eg. the "weight" of 4 mangos becomes 1 mango

mango
papaya

---

papaya
jackfruit

---

mango
jackfruit

that being said... the selection of weighted random items would be like this:

=INDEX(QUERY(SORT(FLATTEN(SPLIT(QUERY(
 REPT(C2:C11&",", A2:A11),,9^9), ", ", 1)), 
 RANDARRAY(SUM(A2:A11)), ), "limit "&E2))

and selection of random non-repeating items would be done like this:

=INDEX(QUERY(SORT(FILTER(C2:C11, C2:C11<>""), 
 RANDARRAY(COUNTA(C2:C11)), 1), "limit "&E2))

---

update:

paste in H2:

=INDEX(QUERY(SORT(FLATTEN(SPLIT(QUERY(
 REPT(C2:C11&",", A2:A11),,9^9), ", ", 1)), 
 RANDARRAY(SUM(A2:A11)), ), "limit 1"))

paste in H3:

=INDEX(QUERY(SORT(FLATTEN(SPLIT(QUERY(
 REPT(C2:C11",", A2:A11),,9^9), ", ", 1)), 
 RANDARRAY(SUM(A2:A11)), ), "where Col1 <> '"&H2&"' limit 1"))

paste in H4 and drag down:

=IFNA(INDEX(QUERY(SORT(FLATTEN(SPLIT(QUERY(
 REPT($C$2:$C$11&",", $A$2:$A$11),,9^9), ", ", 1)), 
 RANDARRAY(SUM($A$2:$A$11)), ), "where not Col1 matches '"&
 JOIN("|", H$2:H3)&"' limit 1")))

and then use:

=OFFSET(H2:H,,,E2)

demo spreadesheet