Slow Array Formula

[fastcar]

Hi All,

I have an array formula that does do what I want but is very slow to run and I was wondering if there is anything I can do to speed it up.

The formula is located on the "RUN SHEET" in the green cells.

What it does is look for "RUN SHEET" K1 on the data tab and then return data to the green cells, so if I change the data in K1 the data in the green cells will change.

The "DATA SHEET" will be quite long maybe up to 1000 rows

Is there anything I can do to speed this up?

Sample File Attached

Thanks

[KOKOSEK]

In similar case, I always create helper array column to find out interesting rows.
Then based on that values by INDEX displaying values from other columns.

	M	N	O	P	Q	R
10	helper	depart site	depart time	Arr Time	To Site	PLANZ Code
11	2	STARTING OFFICE	05:40	06:16	DESTINATION 1	.HWDC.5
12	3	DESTINATION 1	06:31	07:07	STARTING OFFICE	EMPTY
13	4	STARTING OFFICE	07:22	08:02	DESTINATION 2	RY..PRDC.
14	5	DESTINATION 2	08:17	09:07	DESTINATION 1	EMPTY
15	6	DESTINATION 1	10:37	11:27	DESTINATION 2	HWILC.PRDC.
16	7	DESTINATION 2	15:07	15:26	STARTING OFFICE	PRDC..

	M	N	O	P	Q	R
10	helper	depart site	depart time	Arr Time	To Site	PLANZ Code
11	=IFERROR(SMALL(IF('RUN SHEET'!$K$1=DATA!$A:$A,MATCH(ROW(DATA!$A:$A),ROW(DATA!$A:$A)),""),ROWS($A$1:E1)),"")	=IF($M11<>"",INDEX(DATA!$G:$G,'RUN SHEET'!$M11),"")	=IF($M11<>"",INDEX(DATA!$B:$B,'RUN SHEET'!$M11),"")	=IF($M11<>"",INDEX(DATA!$C:$C,'RUN SHEET'!$M11),"")	=IF($M11<>"",INDEX(DATA!$H:$H,'RUN SHEET'!$M11),"")	=IF($M11<>"",INDEX(DATA!$E:$E,'RUN SHEET'!$M11),"")
12	=IFERROR(SMALL(IF('RUN SHEET'!$K$1=DATA!$A:$A,MATCH(ROW(DATA!$A:$A),ROW(DATA!$A:$A)),""),ROWS($A$1:E2)),"")	=IF($M12<>"",INDEX(DATA!$G:$G,'RUN SHEET'!$M12),"")	=IF($M12<>"",INDEX(DATA!$B:$B,'RUN SHEET'!$M12),"")	=IF($M12<>"",INDEX(DATA!$C:$C,'RUN SHEET'!$M12),"")	=IF($M12<>"",INDEX(DATA!$H:$H,'RUN SHEET'!$M12),"")	=IF($M12<>"",INDEX(DATA!$E:$E,'RUN SHEET'!$M12),"")

As you can see there is only array formula in helper column, the rest of columns are displayed based on INDEX.

Your sample file opens (on my pc) around 18 seconds, attached file around 1 seconds.

[Fluff13]

The reason it's slow, is you are looking at entire columns (over 1 million cells).
If you limit it to a reasonable range it will improve performance.
Alternatively you could use use one of the newer functions

Please Login or Register  to view this content.

This would go in A11, but clear the other formulas first.

[Gregb11]

And adding on to what Fluff13 has said, I'd recommend (if it's possible of course), to put your data into an Excel Table. Therefore your formulas expand as your data expands and you're not referencing the whole column, and you'll be referencing exactly the size you need. If you put it in a table and call it "Table1", using Fluff13's formula, it would be:

=LET(f,FILTER(Table1,Table1[Duty ID]=K1),INDEX(f,SEQUENCE(ROWS(f)),{7,2,3,8,5,6}))

Another option is this:
=FILTER(INDEX(Table1,SEQUENCE(ROWS(Table1)),XMATCH({"From Site","Dep Time","Arr Time","To Site","PLANZ Code","Due to Convey"},Table1[#Headers])),Table1[Duty ID]=K1,"No Data")

(I removed your line feed for Dep Time and Arr time headers to make the above work)

Pros/cons of the first formula - you can change the column header names and the formula will still work. However, at some point if you insert, delete or move a column, you'll need to change the formula appropriately.
Pros/cons of the second formula - you can move/delete/insert columns anywhere you want and it will still work (of course as long as you don't delete a column that's being displayed). However, if you change a column header, you'll need to change the formula appropriately.

Attached is a file that shows the results of this (first formula is in A11, the second formula is put in A18 of the attached).