How to improve ?

[jynxy]

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.

=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

 

[Larry Haydn]

For the part of non-ROT data, you could run a loop to check the first 3 characters.
Example:

Sub modCheckString()
Dim lngRowNo As Long
Dim lngLastRow As Long
'-------------------------------------------------------------------------------------
'   Assume your data is in column A, and the column does not have a heading
'   so we begin processing the data from lngRowNo = 1
'-------------------------------------------------------------------------------------
     '-----------------------------
     '   Find the last data row
     '-----------------------------
     lngLastRow = Activesheet.range("A1048576").End(XlUp).Row

     '----------------------
     '   Begin checking
     '----------------------
     For lngRowNo = 1 To lngLastRow
          '---------------------------------------------
          '    If this data does not begin with ROT
          '---------------------------------------------
          If Left(Trim(Activesheet.Range("A" & lngRowNo)),3) <> "ROT" Then
               '--------------------------------------------------------------------
               '   Replace this cell with just the first 3 characters of the data
               '--------------------------------------------------------------------
               Activesheet.Range("A" & lngRowNo) = Left(Trim(Activesheet.Range("A" & lngRowNo)),3)
          End If
     Next
End Sub