Scripting Dictionary help

Thanks for your reply, AB33. I will try to give you more detail on my problem and my proposed solution - maybe you can suggest a better one, if needed. (Apologies for the lengthy post here...)

What I have is a spreadsheet containing a list of companies and addresses, etc. Each company is from a different list (say A and B) - this is shown in two columns. If the company is from list A, it has a unique ID code in column A of the spreadsheet; if it is from list B, it has a unique ID in column B. My challenge is to identify each company on one list that is contained within the other. However, there may be differences in the company names (i.e. company on list A might contain 'The Best Company Ltd', while list B company may be 'Best Company'. So, my concept has been to build a function to score the likelihood that a record is a duplicate (the function works fine for my purposes). I then reinforce this score with a check on the Zip for each record.

Because of the size of the lists, my understanding is that code will run quicker using a dictionary, collection, etc, compared to reading from the sheet directly. Hence, my attempt to use a dictionary object.

So, I'm trying to build 4 dictionary objects: 1 for each list of companies, and a corresponding list for their Zip code. I've been trying to allocate the companies' unique ID as the Key for each item. This (I hope) explains why I have used the same key in the posted code (because there are two dictionary objects shown in the code "SICompanies" and "SIZips" - SICompanies contains the name of the company and it's unique ID as the key while SIZip contains the same unique ID as its key and the Zip of the company - I hope this is making sense!

I'm not sure that your valid suggestion about using 'Exists' will work because of the scoring function that I need to pass both companies to. For your reference, I've included the code that attempts to build all four dictionary objects:

Please Login or Register  to view this content.

Thanks for your continued interest/support...
'Wil'

Sorry Will!
I do not have Experience using collection. I do not know why do you need to use 4 keys, unless you wish to compare data in 4 columns. So far, I am only familar with 2 keys. What some people call them a parent and child. You can use the parent and child to test if an item exists and if so to do someting about it. If it is a parent , you are testing 1 column ony, but if you have a parent and child, you have two columns to test.

For e.g, in your case, if you wish you want to test if a value exists in a cell ,then I want to do something about it (e.g, add it in your case). Remember, keys and items are pairs, so you can not use the same keys twice,,hence the reason for using the exists method to test if the key has already be used(assigned), it does, VBA overwrites the existing key.

You can use collection, instead of dictionary, but my understanding is that you can not retrive items(values) with unique keys.
It looks like your setting is right. You are adding values (items) in to your dictionary, but you eventually need to retirve your items(values) and keys.

there's a good reference here by Patrick Matthews: http://www.experts-exchange.com/Soft...ss-in-VBA.html