Find number of sets of x from a range of quantities

Question

Looking for an excel formula that would automate the creation of sets, each set to have x number of uniuqe colours, let say 4.

e.g.

first set will be 360 of each green, yellow, blue, pink second set will be 120 of each green, white, black, brown (as yellow, blue and pink have been all used up)

Colour	Quantity
Green	800
Yellow	360
Blue	360
Pink	360
White	240
Black	240
Brown	120

Tried to use LARGE(A:A,4), but it's not working that well with a larger set of data.

Answer 1

Very lovely query to work on and think about. Here is one option. I took the liberty to display results of each set in a concatenated fashion (you could adapt this if need be), including the similarity in the form of quantity.

Formula in D1:

=LET(x,LAMBDA(f,r,c,q,n,LET(y,XLOOKUP(n,MAP(q,LAMBDA(i,SUM(N(q>=i)))),q,0,1),z,TAKE(FILTER(c,q>=y),n),IF(y,f(f,VSTACK(r,HSTACK(TEXTJOIN(", ",,z),y)),c,q-y+y*ISNA(XMATCH(c,z,0)),n),r))),x(x,{"Set","Quantity"},A2:A8,B2:B8,B10))

This is a formula that is going to recurse itself as long as there are combinations to be made where n<=colors. This; x(x,{"Set","Quantity"},A2:A8,B2:B8,B10), is the important bit where you can invoke the function using the given parameters.

More in-depth; The idea here is that f(f,r,c,q,n) is recursive where:

• f - Is the function's name;
• r - The current array. You'd notice I start of with just headers and VSTACK() another line in each iteration;
• c - A simple reference to the possible colors;
• q - A simple reference to the starting quantities. You'd notice that each iteration the quantities of those colors that are picked for that particular iteration would drop. I suppose this could be a bit tricky to comprehend so I made a little figure to demonstrate this for n=4:

• n - A reference to the variable that tells the function how many colors need to be combined in each iteration.

---

Below you'll find what the answer (in my interpretation) would be when n=2:

---

Below you'll find what the answer (in my interpretation) would be when n=5: