Scripting Dictionary help

**williams485** · 12-07-2012, 01:21 PM

Hello Excel Forum,

I'm trying to build a very basic de-duplication tool by using two dictionary objects. Each company on my list (there are two lists - a source and one to check against) has a unique ID, which I intend to use as the key in each dictionary (I haven't included the code for generating both dictionary objects).

The problem I'm having is that my intended key is being stored as a value (when I 'watch' the variable the value shows as the intended key). However, when I switch the order of my code I get 'key already used' errors. In the code below column "B" contains the unique ID and column "E" contains the company name. (SICompanies and SIZips are dimensioned as objects):

Please Login or Register  to view this content.

Any help/advice would be much appreciated.

Thanks
Williams485

**AB33** · 12-07-2012, 03:35 PM

Wil,
I am not sure I understand the issue, but on these lines

Please Login or Register  to view this content.

You seem to have used the same key twice (.Range("B1").Offset(i, 0).Value) Unless ofcourse you have two different keys.If this is the full code, why you are adding the items with no reason? My guess is you need to set-up the dictioanary using the "Exists" method, so if the key exists, or not exists, do something i.e, In your case, you are adding the items(values)

**williams485** · 12-11-2012, 06:06 AM

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'

**AB33** · 12-11-2012, 07:00 AM

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.

**williams485** · 12-13-2012, 06:18 AM

I've now got to the bottom of my issue. The solution was twofold. Firstly, when I first posted I thought that my code was storing my intended item as the key - I thought this because the 'watch' window showed the key in the value column. However, I've since learned that despite the misleading display, my items, i.e. the company names, were being stored correctly.

The second part of my problem was my incorrect use of the dictionary object functions and properties: I hadn't paid enough attention to how I was accessing items within the dictionary. I now use an array to store the items of the dictionaries, which pass these to other functions.

So, the final code to build the dictionaries is as follows:

Please Login or Register  to view this content.

Hope this helps someone else...

Williams485

**AB33** · 12-13-2012, 06:34 AM

Glad you have got round in the end.Dictioanary is not simple concept to understand as there not many materials written on them and only a handful of people actually use them on thier code. What I know so far comes from this site's contributors.