Could someone provide me with some pointers as to how refactor the following formula so I don't get the 'Calculation limit was reached...' error.
=MAP(I3:I,J3:J,K3:K,lambda(t,o,i,SUB_LIST(t,o,i)))
SUB_LIST contains the formula (with alias' txt,outval,inval):
=iferror(
ifs(
REGEXMATCH(txt,"\*"),REGEXEXTRACT(txt,"^([\.\s\w]*)\*")&"*",
REGEXMATCH(txt,"\s\- Daz"),"BU Daz "&TRIM(RIGHT(SUBSTITUTE(txt," ",REPT(" ",LEN(txt))),LEN(txt))),
REGEXMATCH(txt,"\sGB24"),"X2 GB24nnnnnn",
REGEXMATCH(txt,"(?i)(\bCLA\b.*\b39)"),"SON 39",
REGEXMATCH(txt,"(?i)(\bCLA\b.*\b43)"),"SON 43",
... [+64 other variants]
REGEXMATCH(txt,"(?i)(^PRO\sPLU.*9HF)"),"ABC 9HF",
((LEFT(txt,3)="OLE")*(inval>0.001)*(REGEXMATCH(txt,"\s82"))),"CKK 82",
((LEFT(txt,3)="OLE")*(inval>0.001)),"CKK 43",
((LEFT(txt,3)="OLE")*(outval<>0)*(REGEXMATCH(txt,"\s82"))),"CKK 82 REP",
... [+4 other variants]
LEFT(txt,4)="AFFI","MIX "&TRIM(RIGHT(SUBSTITUTE(txt," ",REPT(" ",LEN(txt))),LEN(txt))),
LEFT(txt,2)="CB","N (AIR)",
LEFT(txt,12)="VXXFXX NBVCX","VN "&TRIM(RIGHT(SUBSTITUTE(txt," ",REPT(" ",LEN(txt))),LEN(txt))),
... [+6 other variants]
LEFT(txt,5)="SOUTH","WEST "&TRIM(RIGHT(SUBSTITUTE(txt," ",REPT(" ",LEN(txt))),LEN(txt))),
RIGHT(txt,5)="ITIES","PWR "),LEFT(txt,IFERROR(LEN(trim(if(txt<>"",
regexextract(txt,"^[\+\*\-\'\.\/\&\w]*\s?[a-zA-Z]*\s?")))),LEN(TRIM(txt)))))
`
The output of this, after I've removed a couple of variant lines, is just a code phrase of between 6 to 20 characters. That code phrase is then used as the lookup string in another column that creates a further three column helper table.
The single column the MAP function is working through is only about 5000 rows and each string in a cell is no more than 100 characters.
This formula works. However, If I add/copy one more line in between where I've marked [variants] I reach the 'Calculation limit...' error.
I appreciate there are google imposed calculation limits, and, I accept my formula is lazy in that it's written to be maximally maintainable and extendable.
I know it's down to me to refactor but no amount of bashing this formula around has got me any closer to a solution. I'm now out of my depth!
I've tried the more basic 'MATCH' version but doesn't seem to play well with the array (type) MAP function (or not in my trials anyway).
I can't get a 'BYROW' version to work either but that maybe due to the fact that the IFS pairing seemed to get out of hand really quickly to the point I felt I lost the goal of maintainability and extensibility.
I feel sure my refactoring quest is possible but I'm going around in circles here.
Try using a simple ARRAYFORMULA
.
=ARRAYFORMULA(SUB_LIST(I3:I, J3:J, K3:K))