Help improve formula

[jynxy]

Hi,

So im currently recreating a new warehouse rota, its coming along really well and now at the stage where i need to improve and speed up the sheet. Any advise, tips etc are very welcome as im looking to learn from this to improve going forward. Thanks

The first code is

A3 = Name
Teams BHR4 = 080080000080080080080000000800800800800800000000080080080080000800800800800000000800080080080000080080080000000800800800800800000000080080080080000800800000000800800800

=IF($A3<>"","ROT"&IF(LEN(Teams!$BHR4)>0,MID(Teams!$BHR4,MOD(NETWORKDAYS.INTL(Teams!$C4,B$2,"0000000")-1,LEN(Teams!$BHR4)/3)*3+1,3),"000")&IF(LEFT(Jan!FC6,3)="OVT",Jan!FC6,"OVT000")&IF(LEFT(Jan!FC6,3)="SSI",Jan!FC6,"SSI000")&IF(LEFT(Jan!FC6,3)="SSO",Jan!FC6,"SSO000")&IF(LEFT(Jan!FC6,3)="SDS",Jan!FC6,"SDS000")&IF(LEFT(Jan!FC6,3)="HOL",Jan!FC6,"HOL000")&IF(LEFT(Jan!FC6,3)="LID",Jan!FC6,"LID000")&IF(LEFT(Jan!FC6,3)="UNP",Jan!FC6,"UNP000")&IF(LEFT(Jan!FC6,3)="FLD",Jan!FC6,"FLD000")&IF(LEFT(Jan!FC6,3)="MAT",Jan!FC6,"MAT000")&IF(LEFT(Jan!FC6,3)="LIS",Jan!FC6,"LIS000")&IF(LEFT(Jan!FC6,3)="CBR",Jan!FC6,"CBR000")&IF(LEFT(Jan!FC6,3)="ABS",Jan!FC6,"ABS000"),"")

This will generate ROT000OVT000SSI000SSO000SDS000HOL000LID000UNP000FLD000MAT000LIS000CBR000ABS000

The second code takes the first part and breaks down to what is needed. Please note that this information is used between the sheet this is based from and also another sheet.

A9 = Name
BE5 = Date
Shifts Ref = ROT000OVT000SSI000SSO000SDS000HOL000LID000UNP000FLD000MAT000LIS000CBR000ABS000

=IFERROR(IF($A9<>"",
IF(BE$5="","NA",
IF(BE$5<Teams!$C4,"NE",
IF(AND(Teams!$D4>0,BE$5>Teams!$D4),"NE",
IF(IFERROR(NOT(VLOOKUP(BE$5,L_HOLS_SHIFT,2,FALSE)=0),FALSE),"CH",
IF(LEN(Teams!$BHR4)>0,
IF(MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),10,1)="8","OVT",
IF(MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),16,1)="8","SSI",
IF(MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),22,1)="8","SSO",
IF(MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),34,1)="8","HOL",
IF(MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),40,1)="8","LID",
IF(MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),46,1)="8","UNP",
IF(MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),52,1)="8","FLD",
IF(MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),58,1)="8","MAT",
IF(MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),64,1)="8","LIS",
IF(MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),70,1)="8","CBR",
IF(MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),76,1)="8","ABS",
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),4,1)+
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),10,1)+
IF(MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),4,1)="0",
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),28,1),"0")-
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),16,1)-
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),22,1)-
IF(MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),4,1)>"0",
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),28,1),"0")-
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),34,1)-
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),40,1)-
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),46,1)-
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),52,1)-
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),58,1)-
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),64,1)-
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),70,1)-
MID(INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),76,1)
))))))))))),""))))),""),"")

 

[dave3009]

Hopefully this might get you started...

This part of the second formula seems to be evaluated 26 separate times:

INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0))

As the formula is exactly the same each time; you could put this part in a helper cell meaning it would only be evaluated once, this should give you a performance boost.

If you could guarantee that all end-users would be on Microsoft 365, LTSC or 2021 then the LET function would do a similar thing to the helper cell.

 

[jynxy]

@dave3009 thanks for the reply, how does that work with the mid? as its looking at sections of ROT000OVT000SSI000SSO000SDS000HOL000LID000UNP000FLD000MAT000LIS000CBR000ABS000 so you have ROT000 then OVT000 etc

the other thing is this is done with 400 names and each day of the year, which is a lot of calculations

Yes all users will be using excel 365

 

[dave3009]

@dave3009 thanks for the reply, how does that work with the mid? as its looking at sections of ROT000OVT000SSI000SSO000SDS000HOL000LID000UNP000FLD000MAT000LIS000CBR000ABS000 so you have ROT000 then OVT000 etc

the other thing is this is done with 400 names and each day of the year, which is a lot of calculations

Yes all users will be using excel 365

You would only be replacing the repetitive INDEX/MATCH combo, and as you say 400 names being calculated vs 400*26 when repeating the INDEX/MATCH. Quite an overhead saving to be had 400 or 10400... your choice.

With M365 you can use LET, so:

=LET(sp,INDEX(Shifts!$B$3:$NC$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(BE$5,Shifts!$B$2:$NC$2,0)),{put your formula here replacing the INDEX/MATCH with sp})

I'm sure some clever person could build you up a nice LAMBDA, I only wanted to get you started with replacing an inefficient repetitive formula with one that will save you an enormous amount of overhead.

 

[jynxy]

@dave3009 that has helped greatly and removed 5mb off the filesize, thank you for that. i can say i have learnt something new today.

Looking at the first code not sure what can really be done about that, think the biggest overhead on that is the netoworkdays part.