The current post is a follow-up question to this linked one:

Okay. I messed around with a data set of 7 unique monsters (an initial set that's easy to obtain early in the video game). They can't be duplicated in my party but can be combined to make different ones. At the start, there are just 8 slots available for monsters in the video game.

This project focuses on building all the permutations of a "fusion chain" that attempts to take these monsters and arrange them into unique orders for a later combination within this chain.

It starts with A B and then cleans that list to eliminate any B A scenarios from the initial pairings (fusing A B or B A makes the same result). Then, the fusions just tack on C, D, E, F, G, and H (currently broken) to the result of the previous fusion until no more possible fusions remain (having only a single monster in my party).

The problem is this: the query or other functions within the permutation cell throw the error "The resulting array was too large" when attempting to list permutations for sorting 8 monsters at once -- even before the fusions can happen. I have isolated the issue to the formula that sits in H2 on this set of sheets: https://docs.google.com/spreadsheets/d/1JPOw7hag5hpbk2L6UHIzyonyOFkaNQpedzF539Rhqds/edit?usp=sharing

I am currently at a loss for how to fix this problem. I would like to fit at least 8 starting monsters within my sheets for analysis, if not a full 12 for the end of the game.

There is probably a better, more compact way to generate these permutations than the way I have. I would probably like to boot up Excel to try this on my suped-up system and then see where it breaks offline. Yet, I want more efficient formulae to work around my "array too large" issues in Google Sheets. It's where I work best and where I have many other projects.

CodePudding user response：

Stack is a free, volunteer-run service designed to offer "small help" (e.g., some shared knowledge, a tweak to an existing formula or line of code that is almost correct, etc.). It is not designed to be a place where people can come to get custom, complex, time-intensive solutions at no cost.

That said, because of the very recent rollout of new functions, I'm choosing to take this on, mostly for the sake of demonstrating some benefits of some of those new functions.

I'm offering this solution without explanation, as explaining would take far longer than writing the formula.

I've placed the formula below in cell A1 of a new sheet ("Erik Help") within your linked spreadsheet:

=ArrayFormula(IFERROR(TO_TEXT(VLOOKUP(SPLIT(REGEXREPLACE({LAMBDA(x,FILTER(x,NOT(REGEXMATCH(x&"","8|9|0")),x>=12,BYROW(1*(MID(x&LEFT("abc",5-LEN(x)),SEQUENCE(1,4),1)<MID(x&LEFT("abc",5-LEN(x)),SEQUENCE(1,4,2),1)),LAMBDA(row,SUM(row)))=4))(SEQUENCE(34567));REGEXREPLACE("1234567",SEQUENCE(7)&"","")*1;1234567}&"","(.)","$1~"),"~",1,1),{SEQUENCE(7),'chains-original'!A2:A8},2,FALSE))))

Note the use of new functions LAMBDA and BYROW.

For the benefit of future visitors, if the linked spreadsheet should become defunct, this is the portion of the formula that is agnostic of the spreadsheet and will produce all unique combinations of the numbers 1-7, being combinations of from 2 to 7 numbers and not repeating any number within any single result:

=ArrayFormula(SPLIT(REGEXREPLACE({LAMBDA(x,FILTER(x,NOT(REGEXMATCH(x&"","8|9|0")),x>=12,BYROW(1*(MID(x&LEFT("abc",5-LEN(x)),SEQUENCE(1,4),1)<MID(x&LEFT("abc",5-LEN(x)),SEQUENCE(1,4,2),1)),LAMBDA(row,SUM(row)))=4))(SEQUENCE(34567));REGEXREPLACE("1234567",SEQUENCE(7)&"","")*1;1234567}&"","(.)","$1~"),"~",1,1))

If others have a specific question about a specific element of this formula, pose that question in the comments below, and I'll do my best to answer within reason. However, I recommend first sitting with the formula (the spreadsheet agnostic one just above this paragraph) and dissecting it for understanding. Experience is always the best teacher.