+ Reply to Thread
Results 1 to 4 of 4

Array formula too slow. Need an alternative..

  1. #1
    Registered User
    Join Date
    09-30-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    17

    Array formula too slow. Need an alternative..

    Hello,

    I'm using this array formula

    =ISNUMBER(LOOKUP(2^15,SEARCH(" "&TABLES!A:A&" "," "&SUBSTITUTE(SUBSTITUTE(C2,".",""),"-"," ")&" "))) (courtesy: benishiryo).

    for a set of data of 15000 columns . Can any one suggest an alternative for this so that it calculate faster.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,683

    Re: Array formula too slow. Need an alternative..

    May be this...

    =COUNTIF(A:A,SUBSTITUTE(SUBSTITUTE(C2,".",""),"-"," "))


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,135

    Re: Array formula too slow. Need an alternative..

    hi cool_anu4u. i think you were referring to this post below where i provided the solution?
    http://www.excelforum.com/excel-gene...ml#post3422754

    but i didn't range up the entire column like you did here for TABLES!A:A. and i think you meant rows instead of columns. you could either do up a range big enough for your data like:
    =ISNUMBER(LOOKUP(2^15,SEARCH(" "&TABLES!A1:A16000&" "," "&SUBSTITUTE(SUBSTITUTE(C2,".",""),"-"," ")&" ")))
    or give us a sample data of your excel file to let us do the dynamic range. i provided a formula for that too in the other thread, but it might not be customised to your needs

    If that takes care of your original question in that thread, you ought to select Thread Tools from the menu link above and mark thread as SOLVED too. Thanks.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Registered User
    Join Date
    09-30-2013
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Array formula too slow. Need an alternative..

    Thanks benishiryo , the problem was me ranging it up to the entire column.

+ 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] Non array alternative for percentile function
    By vandan_tanna in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-22-2012, 11:43 AM
  2. Alternative to an array formula.
    By RunHard in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-16-2009, 09:18 AM
  3. Array Formula Alternative
    By smninos in forum Excel General
    Replies: 10
    Last Post: 07-15-2009, 04:31 PM
  4. Replies: 4
    Last Post: 01-05-2009, 10:59 PM
  5. [SOLVED] MY ARRAY FORMULA IS SLOW IF DATA LIST IS MORE THAN 10000R0WS
    By S.DURAIVEL - ABU DHABI in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-23-2005, 05:10 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