+ Reply to Thread
Results 1 to 14 of 14

Speed up Value Match Macro

  1. #1
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    O365
    Posts
    311

    Speed up Value Match Macro

    Hello,

    I was wondering if someone could help me speed up the macro in the attached file. What it does is look through a list of amounts and tells you every variation that equals a number you specify and it tells you the location of each number that adds up to the value your looking for. It works really fast if the list is small. But if it is a larger list of numbers it takes a long time because it is testing every combination of the numbers to see if it totals the number you searching on.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Speed up Value Match Macro

    If you have a lot of data, it can be quicker to use .find and .findnext rather than looping through every cell. I can't figure out how to change your code to that but the example in the helpfile is a good place to start:
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    O365
    Posts
    311

    Re: Speed up Value Match Macro

    the code in your example adds two numbers together to see if it equals the value? or adds 10 numbers or more? Or is it just going through the list to find one number that matches the one you're looking for?

  4. #4
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Speed up Value Match Macro

    Actually, maybe try this:
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    O365
    Posts
    311

    Re: Speed up Value Match Macro

    that doesn't seem to be working. it populates one result in cell D2. There should be many results. It could be one cell equals 10 or three cells equaling 10 (4+5+1). etc.

  6. #6
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    O365
    Posts
    311

    Re: Speed up Value Match Macro

    in the file I attached you will see in column C all of the different combinations that equal 10.

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Exclamation Re: Speed up Value Match Macro

    Hi amartin575 !

    I know this code : it isn't yours but it's one of the two fastest I have ever tested !

    So, if you only just had read and apply the points to improve performance
    from the source web page where you grabbed it, you wouldn't open this thread !

    Copy / Paste without burning any neuron is not good for mental health !

    Regards !
    Last edited by Marc L; 04-04-2014 at 12:06 PM.

  8. #8
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    O365
    Posts
    311

    Re: Speed up Value Match Macro

    I wasn't claiming it was mine. I saw it and tested it out on a few numbers and it worked great. On a large set of numbers like 360...the macro takes so long I end up closing it down. I was asking to see if anyone had an idea how to speed it up. I have tried messing with it but it bombs out.

  9. #9
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Speed up Value Match Macro


    With the set of 27 numbers in your attached file, process time took 2 minutes and 33 seconds …

    Just by applying the points to improve performance from the code' source web page,
    with same bunch of data and exactly same code without any mod,
    process needs less than 0.1 second to find out 265 combinations ‼

    What else ?
    Last edited by Marc L; 04-04-2014 at 06:47 PM.

  10. #10
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    O365
    Posts
    311

    Re: Speed up Value Match Macro

    The file works great with the numbers in the file I attached. Try running it on a set of 360 numbers with a number of negatives and positives to help reconcile variances from a bank reconciliation. It gets bogged down.

  11. #11
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Exclamation Re: Speed up Value Match Macro


    I just saw that with your set of 27 numbers process time needed 2 minutes and 33 seconds
    and with the same data and using by its rules the same procedure I need less than 0.1 second !
    Nothing to improve, as I wrote it's one of the two fastest procedure I know …

    But have you any idea of what is a combinatorial sum ?
    How many combinations for a choice of 2 elements to n elements of a set of n elements ?
    It is purely logical and mathematical !
    But I guess you do not even realize 'cause you would not write your last post ‼
    OK, let's see !

    For a set of 27 numbers, there are 134,217,700 combinations !
    [ C(2,27) + C(3,27)+ … + C(25,27) + C(26,27) + 1 ]

    For a set of 360 numbers, there are 213,599E91 (91 zeros follow 213,599 ‼)

    yes, a bit more to calculate, but just a little ! And if you do not respect the process rules
    (don't you read the warning message when you don't respect them ?!), it will take ages ‼

    So you just have to reduce the set of numbers and respect the rules !

    Of course if someone have a better faster algorithm, I wanna to test it !

  12. #12
    Forum Contributor
    Join Date
    10-24-2012
    Location
    Chicago
    MS-Off Ver
    O365
    Posts
    311

    Re: Speed up Value Match Macro

    I am unsure why you assume things and insult. I have a Masters Degree in finance and I manage an accounting group. I think i am quite capable of understanding numbers. If I reduce the data set it runs the macro on how can I be confident it is going to find the match that I am looking for if some of the data is missing? If I am doing a reconciliation and I am off by $27,654.78, how can I remove data to have it run faster and have the confidence that it is finding the right combination or a combination at all because data is missing. I love the macro it just doesn't work for my needs and was asking if anyone had any ideas on making it faster. I believe that is what the site is for? To pick the brains of others to get better results. And I cannot post questions on macros on someone else's post per the forum rules.
    Last edited by JBeaucaire; 04-07-2014 at 08:07 PM. Reason: Removed unnecessary regional slur. Let's all play nice, keep it going folks.

  13. #13
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Speed up Value Match Macro

    Marc L is correct stating the obvious... less data will process faster. 360 numbers is a LOT of combinations. It takes as long as it takes.

    AMartin is correct... less data means incorrect results based on the scenario.


    I will draw the attention of some of our other gurus to have a look.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  14. #14
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Exclamation Re: Speed up Value Match Macro

    Quote Originally Posted by amartin575 View Post
    I am unsure why you assume things and insult.
    Is it a private joke ? First, I do not assume,
    I just see you didn't use the procedure as planned, even with its message warning you :
    "At least 1 negative number is present between positive numbers
    It may take a lot longer to search for matches."
    The reason why in your attached workbook the process took 2 min 33 seconds
    instead of less than 0.1 second for the same data (but in order as explained in the code web page),
    without any mod in this procedure …

    Secondly, I do not insult, I just put combinations numbers on table !
    'Cause of your : « It gets bogged down »
    213,599E91 versus 134,217,700 combinations if not using it as planned, what an extreme huge gap !
    Of course if the data are in order as requested, the procedure needs less combinations to calculate
    but the gap remains in proportion !

    Yes, I forgot to mention to reduce the set of data if you could

    If you can't, it's normal that you have to wait for a long time with so many combinations !

    Edit : the good number of combinations for a set of 360 numbers is 23,485E104 …
    213,599E91 was for a set of 320 … A gap with 13 more zeros !
    Last edited by Marc L; 04-07-2014 at 09:14 PM.

+ 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. Speed-up Macro
    By yunesm in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-22-2011, 04:04 PM
  2. Improving Speed of Index and Match Functions
    By hazza147 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-30-2010, 01:05 PM
  3. Increase speed for match formula on over 130k rows
    By jeffreybrown in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-23-2010, 09:56 AM
  4. Match/Index Calcuation Speed
    By Rochy81 in forum Excel General
    Replies: 1
    Last Post: 10-01-2008, 04:19 PM
  5. [SOLVED] Speed up macro
    By rn in forum Excel General
    Replies: 4
    Last Post: 02-21-2005, 10:25 AM

Tags for this Thread

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