+ Reply to Thread
Results 1 to 26 of 26

Excel getting slow with INDEX array formula

  1. #1
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    68

    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. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    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. #3
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    68

    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.
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,434

    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
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    68

    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. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    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. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,879

    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
    advanced filter to make a copy of the filtered list: http://www.contextures.com/xladvfilter01.html
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  8. #8
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    68

    Re: Excel getting slow with INDEX array formula

    Quote Originally Posted by FDibbins View Post
    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.

    Can you please explain :-

    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. #9
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    68

    Re: Excel getting slow with INDEX array formula

    Quote Originally Posted by FDibbins View Post
    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.

    Can you please explain :-

    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. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,434

    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. #11
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    68
    Quote Originally Posted by FDibbins View Post
    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. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,434

    Re: Excel getting slow with INDEX array formula

    did that speed things up a bit for you?

  13. #13
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    68

    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. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,434

    Re: Excel getting slow with INDEX array formula

    Wrap the formula in IFERROR()

  15. #15
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    68

    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. #16
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    68

    Re: Excel getting slow with INDEX array formula

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

  17. #17
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: Excel getting slow with INDEX array formula

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  18. #18
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,434

    Re: Excel getting slow with INDEX array formula

    Happy to help

  19. #19
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    68

    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.
    Last edited by Lovemyexcel; 02-11-2017 at 12:08 PM.

  20. #20
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    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. #21
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    68

    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. #22
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    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. #23
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    68

    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.
    Attached Files Attached Files
    Last edited by Lovemyexcel; 02-12-2017 at 06:05 AM.

  24. #24
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    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")
    Attached Files Attached Files
    Last edited by JohnTopley; 02-19-2017 at 05:27 AM.

  25. #25
    Registered User
    Join Date
    02-07-2017
    Location
    jeddah
    MS-Off Ver
    2013
    Posts
    68

    Re: Excel getting slow with INDEX array formula

    Quote Originally Posted by JohnTopley View Post
    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. #26
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: Excel getting slow with INDEX array formula

    Glad it gives you the result you required.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Excel Formula: Index Array Formula, Skip Cells
    By clprdctn in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-04-2016, 09:48 AM
  2. Index formula with large range is very slow
    By Josiah in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-22-2015, 02:35 AM
  3. [SOLVED] Slow Array formula
    By gassiusmax in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-09-2015, 12:41 PM
  4. Excel sheet with over 1000 index/match array functions too slow
    By jcmc2112 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-17-2014, 03:17 PM
  5. [SOLVED] Slow index/small/row array
    By Jovica in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-06-2012, 06:59 AM
  6. Index Match Index Formula work slow
    By avk in forum Excel General
    Replies: 9
    Last Post: 03-07-2012, 02:19 PM
  7. Excel array formula, offset, index, match...
    By flippertie in forum Excel General
    Replies: 6
    Last Post: 03-17-2011, 09:42 AM

Bookmarks

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