MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Should excel take this long to process a match index formula?


Posted by Lars on July 17, 2001 3:24 PM

Would writing formulas in VBA go much quicker than regular excel formulas. It takes 3 hours to process to look through approx 2300 rows of data with a match formula. I have a 733 MH with over 400 RAM should excel take this long to process and would
VBA be much wuicker?

Thanks
Lars


Posted by Aladin Akyurek on July 17, 2001 3:30 PM

Lars,

It would surprise me if VBA would run faster in such cases. Damon, Ivan... any comments on this?

Just curious: Would you post the formula that you use?

Aladin

Posted by Mark W. on July 17, 2001 3:48 PM

Sounds like your data might have very few successful
matches and that you're match type (3rd argument)
is 0 or FALSE. Is that the case? Are you interested
in tuning your formula/approach? Can you sort your data
based on the lookup array or column?

Posted by Damon Ostrander on July 17, 2001 6:50 PM

Re: Should excel take this long?

Aladin and Lars,

Aladin is right, VBA should be slower. A lot slower. However, I can not imagine how Excel can be so slow as to take 3 hours with such a small amount of data. "Something is fishy in Denmark."

Occasionally VBA has an advantage over Excel built-in formulas for tasks where an algorithm that is customized and optimized for the particular task at hand can be much more efficient. Usually the only advantage of VBA is the additional or special functionality it can provide.

Yes, I think we should take a look at that formula.

Damon

Posted by Damon Ostrander on July 17, 2001 6:58 PM

Re: Should excel take this long?

Aladin and Lars,

Aladin is right, VBA should be slower. A lot slower. However, I can not imagine how Excel can be so slow as to take 3 hours with such a small amount of data. "Something is fishy in Denmark."

Occasionally VBA has an advantage over Excel built-in formulas for tasks where an algorithm that is customized and optimized for the particular task at hand can be much more efficient. Usually the only advantage of VBA is the additional or special functionality it can provide.

Yes, I think we should take a look at that formula.

Damon

Posted by Ivan F Moala on July 17, 2001 7:30 PM

Aladin & Damon

In general I would have to agree with Aladin & Damon, VBA would be slower, BUT that depends on
what exactly he is trying to do. I can't imagine
it would take this long ?? Definitely something
fishy in Denmark. Need to see what he is trying to do?
Any chance Lars ??


Ivan

Posted by Lars on July 18, 2001 9:36 AM

Re: Should excel take this long?

Alright guys here is the formula:

{=if(iserror(index((carrier1,match($n3&$as3,range1&range2,0))),"-",index(carrier1,match(($n3&$as3,range1&range2,0)))}

and Lars, is right, VBA should be slower. A lot slower. However, I can not imagine how Excel can be so slow as to take 3 hours with such a small amount of data. "Something is fishy in Denmark." Occasionally VBA has an advantage over Excel built-in formulas for tasks where an algorithm that is customized and optimized for the particular task at hand can be much more efficient. Usually the only advantage of VBA is the additional or special functionality it can provide. Yes, I think we should take a look at that formula. Damon : Lars, : It would surprise me if VBA would run faster in such cases. Damon, Ivan... any comments on this? : Just curious: Would you post the formula that you use? : Aladin :

Posted by Aladin Akyurek on July 18, 2001 10:16 AM

Re: Should excel take this long?

{=if(iserror(index((carrier1,match($n3&$as3,range1&range2,0))),"-",index(carrier1,match(($n3&$as3,range1&range2,0)))}

Lars,

Lets try some simplification here:

(1) =IF(ISNUMBER(MATCH($n3&$as3,range1&range2,0)),INDEX(carrier1,MATCH($n3&$as3,range1&range2,0)),"-")

It seems you entered the original formula as an array formula: Is that really necessary?

(2) You may even do better by keeping the #N/A values instead of suppressing them. Additional processing that you carry out on the retrieved data can take those #N/A values into account.

Additionally, as Mark pointed out, try to avoid exact matches. If your data consists of text values with no overlap in parts, you might just omit 0 from the above formulas. If I understood Mark well, you might need to sort the data in such a way that the most frequently retrieved values occur first.

Aladin

Posted by Mark W. on July 18, 2001 11:05 AM

Also...

I'd eliminate range1&range2 from your formula, and
peform the concatenation in worksheet cells that
the MATCH() function would reference as its 2nd
argument. : Alright guys here is the formula

Posted by Lars on July 18, 2001 1:00 PM

Thanks to the both of you I will give it a shot