# Excel getting slow with INDEX array formula

1. ## Excel getting slow with INDEX array formula

Hello ,

I am getting very slow response ( Calculating ( 4 processor (s) ) when using the below formula

Array formula

=IFERROR(INDEX(MASTER!A\$2:A\$25000,SMALL(IF(MASTER!\$B\$2:\$B\$25000=\$L\$3,ROW(MASTER!\$A\$2:\$A\$25000)-ROW(MASTER!\$A\$2)+1),ROWS(\$A\$2:\$J2)),0),"")

Any help to speed the process.

Thank you.

2. ## Re: Excel getting slow with INDEX array formula

Post a SMALL file so we can see the context of your formula.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on "Reply" then GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

3. ## Re: Excel getting slow with INDEX array formula

Attached is a sample file.

1) Data is stored in "MASTER" WorkSheet
2) Search is done in "Search" Worksheet , entry for search is in A3

Thank you.

4. ## Re: Excel getting slow with INDEX array formula

With that much data, and that many ARRAY formulas, Im not surprised your file is slow.

Here is another approach which should speed things up for you, using a helper column in Master.

1. Remove AL of those ARRAY formulas
2. Master K2=IF(A2=Search!\$A\$3,MASTER!K1+1,MASTER!K1)
copied down
3. Search A5=INDEX(MASTER!A:A,MATCH(ROWS(\$A\$1:A1),MASTER!\$K:\$K,0))
4. Turn off manual calc
copied down and across as needed

5. ## Re: Excel getting slow with INDEX array formula

You can test the data by :-
1) changing the Data values in "MASTER" WorkSheet A2:A9 ( e.g 20 , 21 , 24 ) so that in the "Search" Worksheet you can see the processing speed.
2) If you leave A3 blank in the "Search" Worksheet the result will be blank.

Thank you.

6. ## Re: Excel getting slow with INDEX array formula

Try Ford's approach( I was thinking of "helper" column(s) as a possible solution) and let us know the outcome.

7. ## Re: Excel getting slow with INDEX array formula

What is the overall purpose of this formula? It appears to me that "all" it does is filter the list on the value in A3. As quickly as Excel's built in filter tools work, I would be tempted to use those.
autofilter to filter a list in place: http://www.wikihow.com/Use-AutoFilter-in-MS-Excel

8. ## Re: Excel getting slow with INDEX array formula

Originally Posted by FDibbins
With that much data, and that many ARRAY formulas, Im not surprised your file is slow.

Here is another approach which should speed things up for you, using a helper column in Master.

1. Remove AL of those ARRAY formulas
2. Master K2=IF(A2=Search!\$A\$3,MASTER!K1+1,MASTER!K1)
copied down
3. Search A5=INDEX(MASTER!A:A,MATCH(ROWS(\$A\$1:A1),MASTER!\$K:\$K,0))
4. Turn off manual calc
copied down and across as needed
Thanks , I am still getting slow response, maybe, because i did not understand item 1 and 4 of your post.

For Item 1 = I removed the Array and save it as normal formula ( is this correct ?)
For Item 4 = Can you explain how to tun off the manual calc.

9. ## Re: Excel getting slow with INDEX array formula

Originally Posted by FDibbins
With that much data, and that many ARRAY formulas, Im not surprised your file is slow.

Here is another approach which should speed things up for you, using a helper column in Master.

1. Remove AL of those ARRAY formulas
2. Master K2=IF(A2=Search!\$A\$3,MASTER!K1+1,MASTER!K1)
copied down
3. Search A5=INDEX(MASTER!A:A,MATCH(ROWS(\$A\$1:A1),MASTER!\$K:\$K,0))
4. Turn off manual calc
copied down and across as needed
Thanks , I am still getting slow response, maybe, because i did not understand item 1 and 4 of your post.

For Item 1 = I removed the Array and save it as normal formula ( is this correct ?)
For Item 4 = Can you explain how to tun off the manual calc.

10. ## Re: Excel getting slow with INDEX array formula

All Im saying is to delete ALL of those formulas you are using, then, once you gave added my suggested formulas, you can turn auto-calc back on (it is OFF in your file)

For Item 1 = I removed the Array and save it as normal formula ( is this correct ?)[/code]
Not, deleted them completely - start over with blank/empty cells

[code]For Item 4 = Can you explain how to tun off the manual calc.
on the Formula tab/Calculation/Calculation Options, select Auto

11. Originally Posted by FDibbins
All Im saying is to delete ALL of those formulas you are using, then, once you gave added my suggested formulas, you can turn auto-calc back on (it is OFF in your file)

on the Formula tab/Calculation/Calculation Options, select Auto
OK thanks , I did remove all formulas and used yours.
I will turn ON the auto calc too.
Thanks.

12. ## Re: Excel getting slow with INDEX array formula

did that speed things up a bit for you?

13. ## Re: Excel getting slow with INDEX array formula

It did almost 80% speed improvement , thank you.

But I am getting the #N/A in the blank cells , is there any way I can leave this blank for no match values

14. ## Re: Excel getting slow with INDEX array formula

Wrap the formula in IFERROR()

15. ## Re: Excel getting slow with INDEX array formula

It did almost 80% speed improvement , thank you.

But I am getting the #N/A in the blank cells , is there any way I can leave this blank for no match values

16. ## Re: Excel getting slow with INDEX array formula

I just figured out to use the IFERROR command .
Thank you very much for your help.

18. ## Re: Excel getting slow with INDEX array formula

Happy to help

19. ## Re: Excel getting slow with INDEX array formula

Thanks , I will , I still some help in the filtering :- ( it is a bit tricky )

The objective is to arrange the below data with correct order of the dates and times with the LINE1 and LINE2 .

E,g. the First Date Out And Time Out in the Data table below is 09.12.2016 06:40:00 for Track 257 to LINE2 YNN
the Second date out and time out in the Data table below for the same Reg. ZZK17 is 09.12.2016 12:28:00 Track 256 from LINE2

In other words,

The date time out should be following the LINE1 and LINE 2

Here is the Data :

Track Reg. LINE1 LINE2 STAND Date Out Time Out Date Back Time Back Code
256 ZZK17 PAI YNE 35H 09.12.2016 12:28:00 09.12.2016 15:45:00 YY
257 ZZK17 YNE PAI 35H 09.12.2016 06:40:00 09.12.2016 10:26:00 YY
260 ZZK17 SSI VVN 35H 13.12.2016 20:41:00 13.12.2016 23:48:00 YY
260 ZZK17 EEN PPA 35H 28.12.2016 19:50:00 28.12.2016 23:53:00 YY
261 ZZK17 VVN SSI 35H 13.12.2016 14:37:00 13.12.2016 18:19:00 YY
261 ZZK17 PPA EEN 35H 28.12.2016 14:42:00 28.12.2016 18:03:00 YY

I want to sort the above so that the first track that arrives at LINE2 also returns from LINE1 WITH DATE ORDER.

The Result I am looking for is :-

Track Reg. LINE1 LINE2 STAND Date Out Time Out Date Back Time Back Code
257 ZZK17 YNE PAI 35H 09.12.2016 06:40:00 09.12.2016 10:26:00 YY
256 ZZK17 PAI YNE 35H 09.12.2016 12:28:00 09.12.2016 15:45:00 YY
261 ZZK17 VVN SSI 35H 13.12.2016 14:37:00 13.12.2016 18:19:00 YY
260 ZZK17 SSI VVN 35H 13.12.2016 20:41:00 13.12.2016 23:48:00 YY
261 ZZK17 PPA EEN 35H 28.12.2016 14:42:00 28.12.2016 18:03:00 YY
260 ZZK17 EEN PPA 35H 28.12.2016 19:50:00 28.12.2016 23:53:00 YY

NOTICE : the Date Out , Time Out , Date Back , Time Back sequence corresponds with LINE1 and LINE2

The first track in the record was at time 06:40:00 for Track 257 on 09.2.2016 and returned with 256 the same Date . etc..

Thank You.

20. ## Re: Excel getting slow with INDEX array formula

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

21. ## Re: Excel getting slow with INDEX array formula

Hello,

Tried to upload a test file , but getting upload error :-

TEST3.xlsm - Your file of 1.24 MB bytes exceeds the forum's limit of 1,000.0 KB for this filetype.

What should i do?

22. ## Re: Excel getting slow with INDEX array formula

Try ZIPping the file OR simply reduce the number of rows: generally 20-30 rows is sufficient.

23. ## Re: Excel getting slow with INDEX array formula

Unfortunately I was only able to post the data by splitting the data from the formula and result ( 2 workbooks ).

Please insert the data in test1 Sheet to TEST3 (Master) folder at A2.

In TEST3 ( Search ) folder L5 , the formula will give #REF error , that is because there is no data in the MASTER file.

=IFERROR(INDEX(MASTER!#REF!,MATCH(ROWS(\$L\$1:L1),MASTER!#REF!,0)),"")

Once you copy the data from test1 Sheet to TEST3 (Master) folder at A2 , Please change the formula to the below and enter it as array (CTL+Shift+Enter) copy across and down.

=IFERROR(INDEX(MASTER!A:A,MATCH(ROWS(\$L\$1:L1),MASTER!\$L:\$L,0)),"")

Sorry but tried.

Thank you.

24. ## Re: Excel getting slow with INDEX array formula

in L5

=IFERROR(INDEX(MASTER!\$A\$2:\$A\$1000,SMALL(IF(MASTER!\$B\$2:\$B\$1000=Search!\$L\$3,ROW(MASTER!\$A\$2:\$A\$1000)-ROW(\$A\$2)+1,""),ROWS(\$A\$2:\$A2))),"")

Enter with Ctrl+Shift+Enter

Copy down

Change highlighted range to correct ranges in "Master" for the other columns in SEARCH

.... and I don't know why you needed to split these files.

Just re_read you initial post: the answer I gave does not provide sorted data.

Why not simple Custom Sort by the date/time fields???

See "TEST3_SORTED ("Master")

25. ## Re: Excel getting slow with INDEX array formula

Originally Posted by JohnTopley
in L5

=IFERROR(INDEX(MASTER!\$A\$2:\$A\$1000,SMALL(IF(MASTER!\$B\$2:\$B\$1000=Search!\$L\$3,ROW(MASTER!\$A\$2:\$A\$1000)-ROW(\$A\$2)+1,""),ROWS(\$A\$2:\$A2))),"")

Enter with Ctrl+Shift+Enter

Copy down

Change highlighted range to correct ranges in "Master" for the other columns in SEARCH

.... and I don't know why you needed to split these files.

Just re_read you initial post: the answer I gave does not provide sorted data.

Why not simple Custom Sort by the date/time fields???

See "TEST3_SORTED ("Master")
Thank you Sir,

It Works, Really appreciated, I Did Sorted the data and results are awesome.

26. ## Re: Excel getting slow with INDEX array formula

Glad it gives you the result you required.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1