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 this formula (a bit long):
=iferror(if(counta($A$2:$A$13)>=2,arrayformula(query(query(split(flatten(flatten(flatten(flatten(flatten(flatten(
filter($F$2:$F,$F$2:$F<>"")&if(counta($A$2:$A$13)>=3,","&transpose(
filter($A$2:$A$13,$A$2:$A$13<>"")),""))&if(counta($A$2:$A$13)>=4,","&transpose(
filter($A$2:$A$13,$A$2:$A$13<>"")),""))&if(counta($A$2:$A$13)>=5,","&transpose(
filter($A$2:$A$13,$A$2:$A$13<>"")),""))&if(counta($A$2:$A$13)>=6,","&transpose(
filter($A$2:$A$13,$A$2:$A$13<>"")),""))&if(counta($A$2:$A$13)>=7,","&transpose(
filter($A$2:$A$13,$A$2:$A$13<>"")),""))&if(counta($A$2:$A$13)>=8,","&transpose(
filter($A$2:$A$13,$A$2:$A$13<>"")),"")),","),
"where Col1 <> Col2"&
if(counta($A$2:$A$13)>=3," and Col1 <> Col3 and Col2 <> Col3"&
if(counta($A$2:$A$13)>=4," and Col1 <> Col4 and Col2 <> Col4 and Col3 <> Col4"&
if(counta($A$2:$A$13)>=5," and Col1 <> Col5 and Col2 <> Col5 and Col3 <> Col5 and Col4 <> Col5"&
if(counta($A$2:$A$13)>=6," and Col1 <> Col6 and Col2 <> Col6 and Col3 <> Col6 and Col4 <> Col6 and Col5 <> Col6"&
if(counta($A$2:$A$13)>=7," and Col1 <> Col7 and Col2 <> Col7 and Col3 <> Col7 and Col4 <> Col7 and Col5 <> Col7 and Col6 <> Col7"&
if(counta($A$2:$A$13)>=8," and Col1 <> Col8 and Col2 <> Col8 and Col3 <> Col8 and Col4 <> Col8 and Col5 <> Col8 and Col6 <> Col8 and Col7 <> Col8",),),),),),),0),"where Col1 <>''",0)),"not enough data"),)
And the first range this formula was looking at is here in its previously stable form (column F):
| unique init pairs |
|---|
| Pixie,Shikigami |
| Kodama,Pixie |
| Hua Po,Pixie |
| Datsue-Ba,Pixie |
| Angel,Pixie |
| Fomorian,Pixie |
| Kodama,Shikigami |
| Hua Po,Shikigami |
| Datsue-Ba,Shikigami |
| Angel,Shikigami |
| Fomorian,Shikigami |
| Hua Po,Kodama |
| Datsue-Ba,Kodama |
| Angel,Kodama |
| Fomorian,Kodama |
| Datsue-Ba,Hua Po |
| Angel,Hua Po |
| Fomorian,Hua Po |
| Angel,Datsue-Ba |
| Datsue-Ba,Fomorian |
| Angel,Fomorian |
It was provided by a sort of "cleaner" formula I made but that isn't the problem.
The overall input I was testing is like this (in column A) and is also the input for the cleaner formulas for the initial pairs:
| available |
|---|
| Pixie |
| Shikigami |
| Kodama |
| Hua Po |
| Datsue Ba |
| Angel |
| Fomorian |
| High Pixie |
And the expected output... is really big. Here's a sample of the first lines to get an idea (hosted in H2 of the original sheet):
| A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|
| Pixie | Shikigami | Kodama | Hua Po | Datsue Ba | Angel | Fomorian | High Pixie |
| Pixie | Shikigami | Kodama | Hua Po | Datsue Ba | Fomorian | Angel | High Pixie |
| Pixie | Shikigami | Kodama | Hua Po | Angel | Datsue Ba | Fomorian | High Pixie |
| Pixie | Shikigami | Kodama | Hua Po | Angel | Fomorian | Datsue Ba | High Pixie |
| Pixie | Shikigami | Kodama | Hua Po | Fomorian | Datsue Ba | Angel | High Pixie |
| Pixie | Shikigami | Kodama | Hua Po | Fomorian | Angel | Datsue Ba | High Pixie |
| Pixie | Shikigami | Kodama | Datsue Ba | Hua Po | Angel | Fomorian | High Pixie |
| and so on... |
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.
PS: Shout out to Erik for his help with the full sheet and for teaching me some new techniques with LAMBDA and Named Functions. It helped with eventually solving my issue. Stack's policy restricts me from sharing the full spreadsheet for good reason (concerns about bad actors and all that). So, I made edits to this post to make it better contained. Again, sorry for the mess.
CodePudding user response：
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.
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))
CodePudding user response：
There are different algorithms to implement this. See Permutation in computing:
The straight forward and the easiest approach is create a sequence of numbers with BASE equal to the number of items to choose from. For eg, if there are 7 items to choose from, create a sequence like this:
| BASE 7(=ARRAYFORMULA(BASE(SEQUENCE(25),7,7))) |
|---|
| 0000001 |
| 0000002 |
| 0000003 |
| 0000004 |
| 0000005 |
| 0000006 |
| 0000010 |
| 0000011 |
| 0000012 |
| 0000013 |
| 0000014 |
| 0000015 |
| 0000016 |
| 0000020 |
| 0000021 |
| 0000022 |
| 0000023 |
| 0000024 |
| 0000025 |
| 0000026 |
| 0000030 |
| 0000031 |
| 0000032 |
| 0000033 |
| 0000034 |
| .... |
Notice at each position, there are 7 variables(0 to 6) and there are 7 positions. Once we get all the numbers for PERMUTATIONA(7,7), it's a simple matter of removing all the duplicates only getting numbers, where all numbers in each position are unique, i.e., COUNTUNIQUE per number = 7(eg:0124536). Here's a implementation:
=ARRAYFORMULA(LAMBDA(n,QUERY(BYROW(SPLIT(REGEXREPLACE(TO_TEXT(BASE(SEQUENCE(PERMUTATIONA(n,n)-1),n,n)),"\B","."),"."),LAMBDA(r, IF(COUNTUNIQUE(r)<>n,"