UDF MLOOKUP takes too much time. Alternative approach needed.

Hi

I'm using UDF MLOOKUP form
http://www.excelfox.com/forum/f12/vl...iple-values-4/
Basically this is the the same function as lookup, but it returns multiple results in one cell.

I have 2 sheets, where MLOOKUP is used multiple times to construct the reports. But it takes too much time and I had to turn off AutoCalculation on these sheets as the whole Workbook becomes very laggy. An when I turn it on again, it could take more than minute to calculate the results. As my database will become tens of times larger, I am thinking of ways to optimise it.

Could you please advice me other approaches.

Many thanks.


P.S. Here is the code of this UDF.

Please Login or Register  to view this content.

Originally Posted by oeldere

Why not put all data on 1 sheet (instead of 2 sheets).

Than a pivot table can probably help you to do your job.

Separately these sheets take 82 and 45 respectively. If data is in one Sheet the results is 124 seconds. Thus there is only 3 second improvement. But thanks for the advice.

I can't use pivot table, as my data is text and dates for the most part.

Here is the file. The target sheet is "Known Companies".

Originally Posted by oeldere

I think you posted confidential information in your sheet.

Second, where do you have your Mlookup function (in which cell).

Please add also the desired result in your sheet (please no zip file).

1) No there is nothing confidential there
2) MLOOKUPs are in columns C, E, F
3) The desired result would be the same but taking less time to compute
Ans sorry for *zip file, the website did not allow to upload more than 1 mb, so I had to compress it.


Any suggestions are welcomed. Thanks in advance.

After a quick look at your workbook I would suggest it isn't the macro that's dragging the workbook down, it's the formulae you are using.

You have an excessive amount of volatile functions, mainly INDIRECT().

Your workbook is over complicated many sheets and tables are duplications of data already in the workbook, this combined with the volatile functions used to create them is a recipe for failure.

This might seem hard on you, but your workbook needs serious reorganisation.
There is no point in trying to find a solution to a situation that should not exist in the first place.

Originally Posted by Marcol

After a quick look at your workbook I would suggest it isn't the macro that's dragging the workbook down, it's the formulae you are using.

You have an excessive amount of volatile functions, mainly INDIRECT().

Your workbook is over complicated many sheets and tables are duplications of data already in the workbook, this combined with the volatile functions used to create them is a recipe for failure.

This might seem hard on you, but your workbook needs serious reorganisation.
There is no point in trying to find a solution to a situation that should not exist in the first place.

Hi Marcol,

Many thanks for the response.

Well everything works pretty fine and smoothly besides these MLOOKUP functions and I use Indirect almost everywhere. The original list is much bigger and if AutoCalculation for sheets with MLOOKUPs is turned off I do not encounter any problems. Indirect functions are required as data imported can vary and columns can have different order, whereas headings stay the same. So it's either whole bunch of Indirect functions or scripting the search by headers in VBA.

Other thing I can think of is to put data for indirect link directly into the report sheet as text (without formulas), so it won't go the whole way to calculate the route every time.

What do you think?

Originally Posted by Marcol

After a quick look at your workbook I would suggest it isn't the macro that's dragging the workbook down, it's the formulae you are using.

You have an excessive amount of volatile functions, mainly INDIRECT().

Your workbook is over complicated many sheets and tables are duplications of data already in the workbook, this combined with the volatile functions used to create them is a recipe for failure.

This might seem hard on you, but your workbook needs serious reorganisation.
There is no point in trying to find a solution to a situation that should not exist in the first place.

I just made an experiment. Replaced all the Indirect functions from MLOOKUPs and from all the sheets (just pasted values) and it has led to NO improvement in time at all. Not even a second faster. So it is clearly not the case.

Originally Posted by Marcol

Your workbook is extremely difficult to follow, for me at least

Where are you calling the UDF?

In addition to volatile functions, you have 252 named ranges, references to whole columns, (this in 2007 and above is causing 1,048,576 calculations), etc.

The function MLOOKUP() also calls whole columns

Try this first and see if it returns the result you are expecting
This line in particular is returning 1,048,576 for the Ubound value
For r = fFoundNo To UBound(KA1, 1)

I'm pretty sure that most of the code is redundant, but first we need to clearly establish what you are trying to do.

Many thanks for the response Marcol!

My UDFs are in the sheet "Known Companies" in columns C, E and F.

Sorry for making it too complex. Basically the idea is that each company has its unique number and information corresponding to this number which is imported from external and always updated.

Because one company can have more than one note/tag/occasion, lookup and index/match approaches do not work, thats why I turned to mlookup.


Your code alone allowed for 10 seconds improvement from 84 seconds to 74 seconds

During the time I wrote this response I decided to test using limited number of cells in the row instead of the whole row. It calculated everything in 5 seconds. I think you resolved it - reference to the whole row (A:A; E:E etc) was making the whole think laggy. Thank you so much!!!