google sheets - Calculation limit - MAP, LAMBDA, REGEXMATCH - Stack Overflow

admin管理员组

文章数量:1130349

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.

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.

• google-sheets

Share Improve this question Follow asked yesterday DeeKay789DeeKay789 36522 gold badges44 silver badges99 bronze badges 1

• Related; stackoverflow.com/questions/73815258/… – TheMaster Commented yesterday

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Try using a simple ARRAYFORMULA.

=ARRAYFORMULA(SUB_LIST(I3:I, J3:J, K3:K))

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.

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.

• google-sheets

Share Improve this question Follow asked yesterday DeeKay789DeeKay789 36522 gold badges44 silver badges99 bronze badges 1

• Related; stackoverflow.com/questions/73815258/… – TheMaster Commented yesterday

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Try using a simple ARRAYFORMULA.

=ARRAYFORMULA(SUB_LIST(I3:I, J3:J, K3:K))

本文标签： google sheetsCalculation limitMAPLAMBDAREGEXMATCHStack Overflow