+ Reply to Thread
Results 1 to 10 of 10

Index Match Index Formula work slow

  1. #1
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,861

    Index Match Index Formula work slow

    Hellow friends,
    I have using Excel 2007.

    I creating two sheets. (Sheet1 & DATA)

    In DATA sheet : Total 20,000 row record. (sorted : column "C" by order A to Z & column "B" by order newest to oldest) column "C" is code & "B" is date.

    In Sheet1 : I have calculate customer wise recorder. i.e. number of quantity sold & last date of transaction.

    for example : In F6 position I enter formula =SUMIF(DATA!$AI:$AI,":"&$A6&"::"&$F$2&":",DATA!$N:$N)
    In Sheet1 : A6 is code F2, G2, H2, I2, J2 is series.
    In DATA sheet AI column created a key with two columns (=":"&C2&"::"&Q2&":") code & series & column "N" quantity.

    Same formula in column : G6,H6,I6,J6
    =SUMIF(DATA!$AI:$AI,":"&$A6&"::"&$G$2&":",DATA!$N:$N)
    =SUMIF(DATA!$AI:$AI,":"&$A6&"::"&$H$2&":",DATA!$N:$N)
    =SUMIF(DATA!$AI:$AI,":"&$A6&"::"&$I$2&":",DATA!$N:$N)
    =SUMIF(DATA!$AI:$AI,":"&$A6&"::"&$J$2&":",DATA!$N:$N)

    Now
    in column K6 i using formula: =INDEX(DATA!$B:$B,MATCH(1,INDEX(($A6=DATA!$C:$C)*(Sheet1!$K$2=DATA!$Q:$Q),0),0))
    in column L6 i using formula: =INDEX(DATA!$B:$B,MATCH(1,INDEX(($A6=DATA!$C:$C)*(Sheet1!$L$2=DATA!$Q:$Q),0),0))
    in column M6 i using formula: =INDEX(DATA!$B:$B,MATCH(1,INDEX(($A6=DATA!$C:$C)*(Sheet1!$M$2=DATA!$Q:$Q),0),0))
    in column N6 i using formula: =INDEX(DATA!$B:$B,MATCH(1,INDEX(($A6=DATA!$C:$C)*(Sheet1!$N$2=DATA!$Q:$Q),0),0))
    in column O6 i using formula: =INDEX(DATA!$B:$B,MATCH(1,INDEX(($A6=DATA!$C:$C)*(Sheet1!$O$2=DATA!$Q:$Q),0),0))

    In data sheet column B is date, column C is code, column Q is series.
    In sheet1 : column A6 is code, column K2,L2,M2,N2,O2 is series.
    In sheet1 total data row is approx 3000.

    My problem is : when i selecting filter & selecting any type of data i.e. > or < or any data record. it will take calculating / processors time more than 15 to 20 minutes its work very slow.

    Is there a way to faster calculation.

    I have attach sample file with some record.
    Attached Files Attached Files
    Last edited by avk; 03-07-2012 at 02:20 PM.

  2. #2
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: Index Match Index Formula work slow

    Hi

    change in formulas the ranges like $B:$B into real range with data $B$1:$B$38 (the same with Q and C column) it will help
    Regards

    tom1977

    If You are satisfied with my solution click the small star icon on the left to say thanks.

  3. #3
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,861

    Re: Index Match Index Formula work slow

    Hi
    If i changed real range, then every month necessary to change real range every time. That i don't want. Plz. note that, every week data sheet update with new record. Give any other solution.

  4. #4
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: Index Match Index Formula work slow

    So you can always change the ranges to smaller e.g. 1000 rows (bigger than your max weekly rapport with records). Every formula in your sheets works on 1 048 576 rows (when You use filter it is recount in my opinion and this is a cause of slow down)
    Beside changing the range in formula is easy (you simply drag it down)
    other solution could be use of macro which find the correct values without formula.

  5. #5
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,286

    Re: Index Match Index Formula work slow

    Quote Originally Posted by avk View Post
    Hi
    If i changed real range, then every month necessary to change real range every time. That i don't want. Plz. note that, every week data sheet update with new record. Give any other solution.
    Using XL2007 you can use the Table feature and refer to that table in your formulas. he table will automatically adapt when rows are added/deleted.

    Click in you data sheet - Insert Tab - Table You can change the table's name at your convenience
    Be very, very careful using IFERROR ! It hides ALL errors which is not always what you want to get correct results

  6. #6
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,861

    Re: Index Match Index Formula work slow

    I changed real range & after selected filter data record, its take less than 1 minute.
    Secondly plz. let us know to how use macro to find correct values without formula. Plz. note that, i am not familiar with VB. If you give with example, so that i using with our other huge data file with necessary changes.Thanks.

  7. #7
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: Index Match Index Formula work slow

    Hi try this code (run this with sheet1 activate - or better create the button on this sheet)
    It will make the same what your function with index did.
    p.s it is not fast but all the problem with filter should disapear

    Please Login or Register  to view this content.

  8. #8
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Index Match Index Formula work slow

    Try the attached workbook.

    1/. It's probably the SUMIF() that is pulling the performance back
    Try this dynamic formula in Sheet1!F4
    Please Login or Register  to view this content.
    This will dynamically only sum the exact number of rows used, based on the last date entered in column DATA!$B:$B


    2/. I don't think that you are returning the last dates in Sheet1 K:N your formula returns the date of first match found not the last date.
    Try this array formula
    Please Login or Register  to view this content.
    Confirm with Ctrl+Shift+Enter not just Enter
    With 2007 and above this could be
    Please Login or Register  to view this content.
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  9. #9
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,861

    Re: Index Match Index Formula work slow

    Dear TOM, Firstly sorry for delay reply from our side. Yes I used your micro & found that the work done perfectly. Thanks.

  10. #10
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,861

    Re: Index Match Index Formula work slow

    Marcol, Sorry for delay reply. This is also helpful. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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