Here is a link (editable) to a detailed example of what I am stuck with: there is the input, the expected result and comments on the reasoning.
Basically, let's say I have in a column range a list of 1000 elements belonging to three types of object (it would be better if it could also work with more than three types). Each type has an associated probability. I want to sort this input column range so that each element is randomly picked according to their type while keeping the original relative order with this type in the input column, and whenever all elements of a type have been used, then we do the same while ignoring the items from the used up types.
So if there were only two types, with a probability of 0.000001 for Birds and 0.999999 for Insects, then we would expect to find all the Insects in the same order as the original list at the top, and the Birds at the bottom in the same order too.
I'm not sure if this kind of sorting has a name. At first my idea was to use a modified weighed random sort, but I couldn't make it keep the original order of the list for each type.
I have then written a working solution using countif to know how many items from the current type have been selected so far, and then an indirect formula to know which cell value to look at. Sadly, it's incredibly slow (around 20 seconds...), and that's a big no no as I need it to work at a reasonable speed with around 2000 items.
Could anyone help me with a more efficient solution please?
Added one approach in your test sheet(separate tab); Do test it out extensively to see if its holding up for varied scenarios and all...
=let(Ξ,tocol(map(K3:K27,lambda(Λ,let(Δ,lambda(x,index(xlookup(x,G3:G5,E3:E5,,-1))),chooserows(filter(B:B,C:C=Δ(Λ)),countif(Δ(K3:Λ),Δ(Λ)))))),3),
vstack(Ξ,filter(B3:B27,isna(xmatch(B3:B27,Ξ)))))