INDEX MATCH SLOW - Adding Rows (Schedule Template)

[whazzzzzupp17]

I’m in need of some help speeding up my Excel spreadsheet. I have been working on a solution for weeks, and I don’t know what else to do.

About

I track hundreds of project schedules that use the same schedule outline template. Each schedule performs at different time frames. I’m currently doing it on Excel, however, it has become very slow, and will only get slower with historical and other added features. Microsoft Project is not a solution, as it doesn't allow me to track multiple schedules efficiently.

Spreadsheet

There are 3 tabs – Actuals, Projections, Baseline.

Actuals: Input the date a task is started and completed for each project. This is the only tab that involves input
Projections: Calculates the projected start and end dates based on the actual start and completion date. (Index/Match)
Baseline: The original schedule projection based on just the start date of the projected.

The sample schedule that show (5) projects, however, I currently track 100+ schedules and have about 200 other historical schedules.

My speed issues are basically when modifying any columns/rows, because although index/match is not a volatile function, it has to refresh every time I modify Excel to refocus on the new row and column.

I have even gone through every formula and removed both Match functions and put in row and column. Although this helped a lot, it wasn’t much help.


https://ufile.io/wsalc

 

[offthelip]

Have you considered using VBA instead, so that you can control what gets calculated and when. using variant arrays instead of lots of index/match pairs can often make a lot of difference to the speed of a workbook

 

[whazzzzzupp17]

Have you considered using VBA instead, so that you can control what gets calculated and when. using variant arrays instead of lots of index/match pairs can often make a lot of difference to the speed of a workbook

Although, I am not very experienced with VBA I have tried it, but without much luck. I'm probably going the wrong direction. I had a VBA script that would insert the formula into each cell, then copy the values and past the values after calculations to allow me to manipulate the cells, without a formlua. Unfortunately it took forever to calculate, where as my current formula calculates quickly, its just slow.

Would you happen to know how I can speed it up with VBA?

See below for code, although it is from my main spreadsheet, it may not work with the sample.

Sub HSchedule()
   
   
    Range("G20:G37").Formula = _
        "=IF(R19C>100000,"""",IF(RC1=1,WORKDAY(INDEX(R18C[1]:RC[1],RC2),1+INDEX(SchedulesTable,ROW()-ROW(RC5),R5C[1]),Holidays)," & Chr(10) & "IF(RC1=2, MAX(WORKDAY(INDEX(R18C[1]:RC[1],RC2),1+INDEX(SchedulesTable,ROW()-ROW(RC5),R5C[1]),Holidays),WORKDAY(INDEX(R18C[1]:RC[1],RC3),1+INDEX(SchedulesTable,RC5,R5C[1]),Holidays))," & Chr(10) & "MAX(WORKDAY(INDEX(R18C[1]:RC[1],RC2),1+INDEX(SchedulesTable,RC5,R" & _
        "5C[1]),Holidays), WORKDAY(INDEX(R18C[1]:RC[1],RC3),1+INDEX(SchedulesTable,RC5,R5C[1]),Holidays), WORKDAY(INDEX(R18C[1]:RC[1],RC4),1+INDEX(SchedulesTable,RC5,R5C[1]),Holidays)))))" & _
        ""
    Range("G19:G37").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

 

[whazzzzzupp17]

Here is the array formula for each column. The top one was for one column

Sub HSchedule()
   
   
For ColNum = 7 To 500 Step 2
    Range(Cells(3, ColNum), Cells(1159, ColNum)).Formula = _
    "=IF(R19C>100000,"""",IF(RC1=1,WORKDAY(INDEX(R18C[1]:RC[1],RC2),1+INDEX(SchedulesTable,ROW()-ROW(RC5),R5C[1]),Holidays)," & Chr(10) & "IF(RC1=2, MAX(WORKDAY(INDEX(R18C[1]:RC[1],RC2),1+INDEX(SchedulesTable,ROW()-ROW(RC5),R5C[1]),Holidays),WORKDAY(INDEX(R18C[1]:RC[1],RC3),1+INDEX(SchedulesTable,RC5,R5C[1]),Holidays))," & Chr(10) & "MAX(WORKDAY(INDEX(R18C[1]:RC[1],RC2),1+INDEX(SchedulesTable,RC5,R" & _
    "5C[1]),Holidays), WORKDAY(INDEX(R18C[1]:RC[1],RC3),1+INDEX(SchedulesTable,RC5,R5C[1]),Holidays), WORKDAY(INDEX(R18C[1]:RC[1],RC4),1+INDEX(SchedulesTable,RC5,R5C[1]),Holidays)))))" & _
    ""
    Range("G19:G37").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Next ColNum
End Sub

 

[offthelip]

Looking at your formula I am not surprised your workbook is slow. The good news is it looks like exactly the sort of problem that can be speeded up using the variant arrays and VBa.
To give you an idea of how to get started look at this thread on how reprogram a vlookup in VBa using varinat arrays

https://www.mrexcel.com/forum/excel-questions/1043185-vlookup-vba-alternative.html

 

[whazzzzzupp17]

I'm so lost with VBA. It took me a few days just to get the previous one going. I understand formula's but VBA just makes my mind go crazy.

Is there anyway you can help me out. I'll honestly spend hours for something minimal without realising it.

 

[offthelip]

Looking at your formula and from what you describe it looks like your system is a complicated system that needs a complete redesign to get it to work efficiently and fast. This forum isn't really the right place to get that sort of help.
I certainly don't have the time to take on a project like that, I usually spend a maximum of 10 to 15 minutes a day looking at this forum. I suspect that reworking your system is going to take hours even for sombody who is very familar with VBA. So my suggestion is get some professional help and pay a VBA expert to redesign your system. The only alternative is to make an effort to learn vBA and get familar with it so that you can do it yourself. Obviously this forum is always here to help with specific questions and problems. Sorry about that

 

[whazzzzzupp17]

Looking at your formula and from what you describe it looks like your system is a complicated system that needs a complete redesign to get it to work efficiently and fast. This forum isn't really the right place to get that sort of help.
I certainly don't have the time to take on a project like that, I usually spend a maximum of 10 to 15 minutes a day looking at this forum. I suspect that reworking your system is going to take hours even for sombody who is very familar with VBA. So my suggestion is get some professional help and pay a VBA expert to redesign your system. The only alternative is to make an effort to learn vBA and get familar with it so that you can do it yourself. Obviously this forum is always here to help with specific questions and problems. Sorry about that

Is there anyway you can help me with this and I can return the favor somehow? The formula I use is actually very basic, it is just repeated 3 times because of an if statement. It is very critical that I figure this out in the next day or so, and you're the only person giving feedback on how to solve it. My spreadsheet is pretty much unworkable right now.

The formula is basically this:

MAX(
WORKDAY(INDEX(I$5:I13,MATCH($B14,$F$5:$F$504,0)),1),
WORKDAY(INDEX(I$5:I13,MATCH($C14,$F$5:$F$504,0)),1),
WORKDAY(INDEX(I$5:I13,MATCH($D14,$F$5:$F$504,0)),1))

 

[offthelip]

Here is some code that does more or less the same as your equations. although your equations are slightly doubtful since you index array is a lot shorter than your match array.
However I must point out that the benefits of using variant arrays to solve this problem only works if you can get rid of most of the equations in the entire workbook and thus seriously reduce the recalcualtion time.

Sub test()

inarr = Range(Cells(1, 1), Cells(504, 9))
tb14 = inarr(14, 2)
tc14 = inarr(14, 3)
td14 = inarr(14, 4)



Max = 0
 For i = 5 To 504
  If inarr(i, 6) = tb14 Then
   If inarr(i, 9) > Max Then
   Max = inarr(i, 9)
   End If
  End If
  If inarr(i, 6) = tc14 Then
   If inarr(i, 9) > Max Then
   Max = inarr(i, 9)
   End If
  End If
  If inarr(i, 6) = td14 Then
   If inarr(i, 9) > Max Then
   Max = inarr(i, 9)
   End If
  End If
Next i
 
MsgBox ("max value is " & Max)



End Sub