FILTER Function Lagging, alternative function for this case?

[SJNSkytanking]

Hi all,

This is really difficult for me to explain, but i've tried my best. If anyone can help, I will be eternally grateful and you will save my sanity.

I have the attached sheet which is a timeline task planner. Currently I'm using a FILTER function in each of the cells on the timeline to calculate whether multiple criteria are met, and if they are, to return a value (team number), which is then conditionally formatted based on the number of the team.

When a team number is entered into the "TEAM" column (Column G), it then shows on the timeline.

The problem is that the FILTER function is lagging a lot, probably because of the number of cells the formula is in (thousands)

Is there an alternative formula I can use rather than the FILTER function?

Below is an example formula used:

This searches the column G for the matching team number, then if the matching cell in column D is a time more than or equal to the matching cell in the timeline (in this case column O), then checks for the time in column F and if it is equal to or less than the time in the timeline. If all are matched, then it returns the team number. Conditional formatting then changes the colour based on the team number.

=@FILTER( $G$3:$G$41,
        ($G$3:$G$41=$J3)
       *($D$3:$D$41<=O$2)
       *($F$3:$F$41>=O$2), "" )

Hopefully this can make sense to someone!

Thank you all for your help.

Sam

[#2]

@SJNSkytanking 

FILTER may not be the best fit for this task, but I don't believe it's causing a "lag" in the workbook.  I timed several full recalculations on the workbook and the average time was about .15 seconds.

Are you referring to a "lag" in navigating the sheet and moving from cell to cell, etc.?  The sheet does have a lot of objects (200+) that will slow navigation:

[#3]

@Patrick2788 

Thank you for your response!

Do you have an idea what other function could be used?

The lag I'm talking about is the delay between entering a number in the Team column (G), and the timeline part of the sheet updating, especially when quickly entering team numbers.

I have attached a screen recording to demonstrate. Note that as soon as I type the number, I press enter immediately, however it has a delay. 

In regards to the shapes, even with the shapes removed, it is the same.

Many thanks for your help and time!

Sam

[#4]

@SJNSkytanking 

As @Patrick2788 already stated the lag is only a "long" fraction of a second.

And that is because of 84240 formulas. If you remove alle formulas the lag would be gone.

[#5]

I thought this might be the case, in which case i'm out of luck.

However I just tried deleting rows 42 onwards, vastly decreasing the amount of formulas, and it is still the same?

[#6]

Well, you could remove all cond. form. rules.

[#7]

@SJNSkytanking 

Another way to approach the sheet is to produce the results all in 1 spill. This approach has reduced the size of the workbook to less than 1 mb.

=LET(
    height, COUNT(ETA),
    width, COLUMNS(times),
    GetResults, LAMBDA(r, c,
        LET(
            team_number, INDEX(sched_team, r),
            interval, INDEX(times, , c),
            FILTER(
                team,
                (team_number = team) * (interval >= ETA) *
                    (interval <= ETD),
                ""
            )
        )
    ),
    MAKEARRAY(height, width, GetResults)
)

A single input to get the results:

Other ways to speed up the workbook:

1. Shorten the conditional formatting ranges.  Conditional formatting is an old feature that calculates on a single-thread.

2. Remove unnecessary objects from the sheet. 

[#8]

@SJNSkytanking As others have already pointed out, the lag is likely due to the sheer number of formulas being used, either directly in the worksheet, or via conditional formatting. However, there are a few changes that can be made to help improve performance. In column G, for example, all 9 of the conditional formatting rules can be applied to range $G$3:$G$1211 instead of $G$3:$G$9999, as the data currently only extends to row 1211. Also, regarding the conditional formatting logic for output range $O$3:$IT$1211, rather than having 10 different rules to change the color based on the team number, start by filling each row in the range with the applicable colors for each team, then use only 2 rules to change the color to either grey or white for cells containing "".

As for finding a better suited formula for this scenario, I did a few tests using XLOOKUP, MAKEARRAY and COUNTIFS. XLOOKUP performed about the same as FILTER with only 9 days' worth of formulas input (84,240 cells); MAKEARRAY nearly crashed Excel when applied to the same range; and surprisingly, COUNTIFS outperformed them all. COUNTIFS was able to handle the entire data set (290,160 cells, split into blocks of 9,360 cells per day) and still have less of a lag than FILTER did with only 9 days. With MS365, the COUNTIFS function is also capable of spilling its results dynamically when passing a range of values to each criteria argument (provided the setup is correct). For example, inputting the following formula in cell O3 will spill the results for the entire first day:

=IF(COUNTIFS(G3:G41, J3:J41, D3:D41, "<="&$O$2:$IT$2, F3:F41, ">="&$O$2:$IT$2), J3:J41, "")

The formula can then be copied and pasted to the first cell of each day thereafter (O42, O81, O120, etc.). I've attached a copy of the workbook for your convenience. Please try it out and make your own judgement as to whether or not the fraction of a second lag is tolerable now...

[#9]

I think you can split sheet1 from one to two workbooks because of the size of whole workbook ,one master list only for data and another as report which extract partial data at once from master list rather display all data in one sheet very times.