Hi,
How can i improve this formula, it is very resource intensive, its used 400x31 (12,400) times. So far with help i have halved the file size, and improved about 20% on performance thanks to help from others on here for there suggestions. i believe this is the last part that could be improved on. so any suggestions are most welcome.
Here is an example string ROT080OVT000SSI000SSO000SDS000HOL080LID000UNP000FLD000MAT000LIS000CBR000ABS000
Rot is the rota pattern, anything else would remove the rota hours i.e if HOL080 it should show as HOL.
Thanks
How can i improve this formula, it is very resource intensive, its used 400x31 (12,400) times. So far with help i have halved the file size, and improved about 20% on performance thanks to help from others on here for there suggestions. i believe this is the last part that could be improved on. so any suggestions are most welcome.
Excel Formula:
=IFERROR(LET(pattern,INDEX(Shifts!$I$3:$NJ$402,MATCH($A9,Shifts!$A$3:$A$402,0),MATCH(C$5,Shifts!$I$2:$NJ$2,0)),IF($A9<>"",IF(C$5="","NA",IF(C$5<Teams!$C4,"NE",IF(AND(Teams!$D4>0,C$5>Teams!$D4),"NE",IF(IFERROR(NOT(VLOOKUP(C$5,L_HOLS_SHIFT,2,FALSE)=0),FALSE),"CH",IF(LEN(Teams!$BHR4)>0,IF(MID(pattern,10,1)="8","OVT",IF(MID(pattern,16,1)="8","SSI",IF(MID(pattern,22,1)="8","SSO",IF(MID(pattern,34,1)="8","HOL",IF(MID(pattern,40,1)="8","LID",IF(MID(pattern,46,1)="8","UNP",IF(MID(pattern,52,1)="8","FLD",IF(MID(pattern,58,1)="8","MAT",IF(MID(pattern,64,1)="8","LIS",IF(MID(pattern,70,1)="8","CBR",IF(MID(pattern,76,1)="8","ABS",MID(pattern,4,1)+MID(pattern,10,1)+IF(MID(pattern,4,1)="0",MID(pattern,28,1),"0")-MID(pattern,16,1)-MID(pattern,22,1)-IF(MID(pattern,4,1)>"0",MID(pattern,28,1),"0")-MID(pattern,34,1)-MID(pattern,40,1)-MID(pattern,46,1)-MID(pattern,52,1)-MID(pattern,58,1)-MID(pattern,64,1)-MID(pattern,70,1)-MID(pattern,76,1)))))))))))),""))))),"")),"")
Here is an example string ROT080OVT000SSI000SSO000SDS000HOL080LID000UNP000FLD000MAT000LIS000CBR000ABS000
Rot is the rota pattern, anything else would remove the rota hours i.e if HOL080 it should show as HOL.
Thanks