Creating Combinations Based on Certain Criteria?

Hi Jack,

I've gone down the approach of generating all possible combinations of 3 items from the list you have there using formulas (as you can see attached file, sheet OUTPUT)

Columns A:C generates all possible combinations of 3 items from a list of 18.

Formula:

A1 = 1, B1 = 2, C1 = 3

A2: =IF(B2=18-1,A2+1,A2)

B2: =IF(C2=18,IF(AND(C2=18,B2=18-1),A3+1,B2+1),B2)

C2: =IF(C2=18,B3+1,C2+1)

Note: This can be extended by finding all "18s" in the formula and replacing them with a different number. Then use the formula =COMBIN(NewNumber , 3) to figure out how many more rows you need to add to the bottom of the list.

Columns E:G converts them into alphabets, as per your table using a simple INDEX. Same with columns I:K (prices) and N:P (ratings)

Formula:

E2: =INDEX('Sample Data (Before)'!$A$2:$A$19,A2)

I2: =INDEX('Sample Data (Before)'!$C$2:$C$19,A2)

N2: =INDEX('Sample Data (Before)'!$B$2:$B$19,A2)

I then calculate find the largest rating that are below 18,000 using a MAX IF on column L (sum of prices) and column Q (sum of ratings)

Formula:

=MAX(IF($L$2:$L$817<=T$2,$Q$2:$Q$817))

Note: This is an array function, and as such, as you type the formula, hit CTRL SHIFT ENTER to finalise the formula rather than just enter.

To address the possibility of multiple solutions, I've also prepared a table to give you 5 possible solutions using

Formula:

U7 (row number of solution) =IF(T7<=$T$5,LARGE(INDEX(($L$2:$L$817<=$T$2)*($Q$2:$Q$817=$T$4)*ROW($Q$2:$Q$817),0),T7),0)

where T5 is a COUNTIFS of how many solutions there are

T5: =COUNTIFS($L$2:$L$817,"<=" & $T$2,$Q$2:$Q$817,$T$4)

and finally extracting out the solution

V7: =IF($U7=0,0,INDEX(E$2:E$817,$U7-1))

W7: =IF($U7=0,0,INDEX(F$2:F$817,$U7-1))

X7: =IF($U7=0,0,INDEX(G$2:G$817,$U7-1))

Or you can just see attached.

P/S: Clearly, I have too much free time to kill today.