For example:

I've got a value of 15 and I want to see what 9 number values can go into 15. By hand I generate it this way:

(Note: Maximum Value can be 5 (Increments bigger later on), Minimum Value can be 0)

As you can see this can be tedious to do long hand. Especially considering that I have to do this for values 15 - 51.

I've been using the Transpose function to transpose arrays I've created before, but that was to transpose an array into a Random order:

=TRANSPOSE(SORTN(TRANSPOSE(B22:I22),8,,RANDARRAY(8),))

Looking for an Excel answer I came across this, but it uses an Add-On to do so:

https://www.extendoffice.com/documents/excel/3557-excel-find-all-combinations-that-equal-given-sum.html

Why I'm doing this?

I'm personally creating a character generator for Chronicles of Darkness Generic Monsters. Monster traits are numeric values with a trait limit of 5 at Lvl 1 which goes up to 10 at lvl 6 and doesn't increase from lvl 6 to 10. The total value of traits to distribute at lvl 1 is between 15-18, 19-22 at Lvl 2, 23-26 at lvl 3, etc... increasing by 4 each level till lvl 10 where its anything over 51 (But this doesn't have to be included in my generator).

If you have any questions feel free to ask.

Please bear with me as I try my best to explain what I'm trying to do...

I want to analyze some data from a video game. The player can equip four types of armor, and each piece of armor has an associated weight value. A simplified example of the data table I'm working with is below.

I'd like to generate a list of all possible combinations of gear that the player can wear, and also find the total weight of all equipped pieces for each permutation. Unfortunately, each category has between 25-50 available items, which means the number of possible combinations is HUGE.

I found that the example here works well for generating the list of combinations. I slightly modified the formula to help separate and identify the items in the resulting string. It takes a long time for the list to fill in, but I think that's going to be a problem no matter what due to the sheer number of results.

=INDEX(FLATTEN(FLATTEN(FLATTEN("{{Head}}"&
 FILTER(A2:A, A2:A<>"")&"{{Chest}}"&TRANSPOSE(
 FILTER(C2:C, C2:C<>"")))&"{{Arms}}"&TRANSPOSE(
 FILTER(E2:E, E2:E<>"")))&"{{Legs}}"&TRANSPOSE(
 FILTER(G2:G, G2:G<>""))))

The formula generates a list that looks like this:

{{Head}}Bare head{{Chest}}Bare chest{{Arms}}Bare arms{{Legs}}Bare legs
{{Head}}Bare head{{Chest}}Bare chest{{Arms}}Bare arms{{Legs}}Socks
{{Head}}Bare head{{Chest}}Bare chest{{Arms}}Bare arms{{Legs}}Boots
{{Head}}Bare head{{Chest}}Bare chest{{Arms}}Gloves{{Legs}}Bare legs
{{Head}}Bare head{{Chest}}Bare chest{{Arms}}Gloves{{Legs}}Socks
...

Great, step one done. However, once the list is made, I don't know the most efficient way to look up and find the sum of the weights for each combination. I can extract each item from the combined string with some MID and FIND manipulation, and then use VLOOKUP or INDEX/MATCH to get the associated weights, then add them all together. But the formula is clunky and significantly adds to the sheet's already long processing time. A smaller test sample set eventually outputs the information I need, but with the full set of data the sheet takes several minutes to load and eventually crashes. I know for sure that my sheet has enough rows for the list to expand into, so that's not the issue.

I also found this example of a script that does more or less the same thing as the INDEX/FLATTEN/FILTER/TRANSPOSE formula. I thought it might be faster, but testing with a shorter list results in "result too large" error message, so it won't be able to handle my full list. Even if it could handle it, the script assumes all four columns have the same number of rows, so it would still need to be modified. And besides, this doesn't address my need for a better way to extract and add up the weights of each possible combination.

Does anyone have a better idea how to process this mass of information? Is there a better set of formulas I should be using or scripts that can handle my needs without bogging down the sheet to the point that it's unusable?