Using arrays with application.vlookup still taking time

[Ranmandaman]

Hi All

I'm new to the forum and hoping to get some help please.

I've got the following code in VBA which I thought would save me alot of time when doing vlookups as it is all being stored in an array. But it still takes A LONG time to run through. Am I doing something wrong here?

Please Login or Register  to view this content.

Thanks

[zbor]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code in [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

[nilem]

Hi Ranmandaman,
try it

Please Login or Register  to view this content.

[Ranmandaman]

Hi Nilem

That's brilliant. It speeds up the lookup so much.

Why did my original array lookup run so slow? I would have thought looking up array in memory would be much faster. It was slower then doing a vlookuo formula on the sheet itself!

Also I notice that using the scripting dictionary not all of the correct values from the data array are bein returned. Like it will just return the lookup value rather than data(i,3). I tried to research more into scripting to see if I could fix but sadly haven't been able to. Could you help out please!

Thanks!

[nilem]

You are right, the arrays are fast. But using worksheet function in a loop is very slow code execution.
Could you show an example file where the code produces incorrect results.

[Ranmandaman]

Hi Nilem. Sorry for late reply. It's a bit difficult to send an example without spending a while changing all the confidential data. But I can explain how the file works. The data array is basically 3 columns. A is the counterparty name, B is the subparent and C is parent. The dictionary script looks up all the items in lookup array against data array and should take the Parent. I notice that for many of the items it still keeps the lookup item (ie the counterparty name) rather then the parent. Each counterparty in the data array is different but there are duplicated parents as you would imagine

Does this help?

[nilem]

Hi Ranmandaman
If you want the code to run as the Vlookup (or as your code in the first post), uncomment ': .Remove Data(i, 1). That is, must be so

Please Login or Register  to view this content.

[Ranmandaman]

I've found out why it happens. The lookup array contains duplicated items (as expected). So when the

Please Login or Register  to view this content.

is set it only is set to the last one and when

Please Login or Register  to view this content.

is set it only will be set for the last row for the duplicated value. Any advice on how to fix this?

[Ranmandaman]

Hi Ranmandaman
If you want the code to run as the Vlookup (or as your code in the first post), uncomment ': .Remove Data(i, 1). That is, must be so

Please Login or Register  to view this content.

Yep already removed the comment. Issue is due to the above (I think!). Any ideas how to overcome?

[nilem]

Ranmandaman, here can be lot of options. Try to jot down a small example file (replace your data on, for example, apples, bananas, etc.), and show that you want to get.

[Ranmandaman]

Hi

Sorry for the long delay between replies. I've created a small file to give an example of the issue.

The attached example contains a mapping table in cells A1:C7

I want to lookup each item in cells D1:D7 against A1:A7 and return the corresponding item in column C to column E. As you can see in column D I have duplicate items and using the dictionary method only the last duplicated item is correctly mapping.

I would like to see it give the result as below

Apple Fruit
Pizza Food
Apple Fruit
Mango Fruit
Pizza Food
Apple Fruit
Mango Fruit


Hope this helps to explain the problem!
Thanks

[nilem]

Hi Ranmandaman,
try

Please Login or Register  to view this content.

[Ranmandaman]

Thats excellent!! It seems so simple now that youve shown me but I couldnt figure out how to do it lol

Will try that code against my main data but it worked a charm on the example.

Thanks Nilem!

[heggy]

I suggest you add the following line to your code before any other code lines.
This will turn off screen updating.
Application.ScreenUpdating = False

and then add
Application.ScreenUpdating = True

right before your "End SUB" line

[Ranmandaman]

Already done! Thanks!

[Ranmandaman]

Sorry to come back to this! I've had to change the Iif formula to a regular If because it was adding items to the dictionary after it couldn't find it the first time. And then it would return a empty value against it. Very strange - would you know why that is?

[nilem]

Hi Ranmandaman,
could you give an example where the code is not working correctly

[Ranmandaman]

Hi Nilem - not to worry I just changed the Iif to a normal If statement. I have another question however. For some reason the loading of the array into the dictionary is
Really slow for some of the files I use the code in but not on others. The files are all very similar and the usual application.screenupdating is false etc