Compiling and accessing nested collection within a nested dictionary

*Cleaned up the thread. I was having trouble posting my first thread over the last couple days, so I quickly threw this one together - then it posted (finally).*

Hi,
First time posting a thread, long time user/reader of threads. I need help solving a problem that I can't find the answer (or close to the answer) to.

I have a data-set in a table with 5 columns I care about: Part numbers, Quote date, Quote quantity, price and supplier.
The first dictionary [DICT1] would host part numbers, with the item being quantity ranges - also I'm presuming as a dictionary [DICT2]. Within the nested dictionary, there will be a collection of the three other variables (date, price, supplier) that is DICT2's item.

If the supplier is the same, the latest price based on quote date will apply and be updated in the collection accordingly.
If the supplier is different, the lowest price regardless of quote date will apply and be updated in the collection accordingly.

Not all DICT1 keys will have all of the keys available in DICT2 (see comparison in the attached sample spreadsheet).
Pivot tables won't work because it will show all suppliers who have a quantity range price, not just the lowest/latest.

When the table is evaluated, the output will be the information collected in the dictionary. The format could be something like what is shown in the attached sample file (starting in column K).

Given (hard-coded in macro for now):
Quantity Ranges (ie 1 to 5, 6 to 10, etc)
6 month age restriction for evaluating the data. (older quotes that are in the table will be skipped over).

I am struggling to access the Coll items in DICT2 for evaluation. Also struggling to dump the information collected into an output. Any help or suggestions would be fantastic!

Cheers!

I don't know your purpose to mix nesting dictionary and collection, you can solve the problem just using dictionary alone or just collection alone.

Using dictionary :

Please Login or Register  to view this content.

Using collection :

Please Login or Register  to view this content.

Karedog- First, thanks! I can't believe how fast the code is. I originally tried using nested dictionaries alone, then quickly got lost trying to wrap my head around the syntax. I appreciate the help!! What you wrote was exactly what I was trying to originally do. In your opinion, is dictionary or collection better in this case?

Note: I don't think the if..then algorithm to determine if the supplier is the same of the price is higher is quite right. I'll tinker with it a bit to see if I can get results I expect. The basic coding you provided is perfect though!

Update 2:
I can't seem to validate the algorithm to a manual check of the real data. I'll keep playing with the if..then statement. In the meantime, here is the logic:

If a supplier at a given qty range is the same, use the latest date information to decide whether to replace existing data in the collection/dictionary. There can be multiple quotes from the same supplier in the dataset. I want to use the latest date information for that specific qty range in this case.
If the supplier is different at that qty range (either null or a different supplier), use the lowest price to decide when to replace existing data in the collection/dictionary.

Scenarios:
S1 = current supplier at qty range "x", S1 date is newer than current date, replace Price with price at date regardless of which is higher. Otherwise, move to next record.
S1 <> current supplier at qty range "x", S1 price is lower than the current price, replace current price with S1 price, current supplier with S1 supplier, current date with S1 date, etc.

If it helps, I can send better "real life" data in a test file, with made up supplier names and part numbers to protect the innocent. Even with a table that has 4900+ lines, your code takes less than a second (impressive)!

Update 3:
I figured out the if.then statement yesterday. For posterity:
If (b(1) = "") Or (b(1) <> a(i, 5) And b(0) > a(i, 4)) Or (b(1) = a(i, 5)) Then
should be
If (b(1) = "") Or (b(1) <> a(i, 5) And b(0) > a(i, 4)) Or ((b(1) = a(i, 5)) And ((b(2) >= a(i, 2)))) Then
'if ((supplier is null OR supplier is different) and $ is greater than current $) OR (supplier is same AND quote date is newer than current date)) then

Thanks for the help!!

You are welcome. Both can be worked nicely, you can choose the one that you like it better.

Well, I thought that "the latest price based on quote date", the latest here mean the latest row of the same quote date (the way the data is inputted, the latest must be at the very bottom), glad you can found the solution by yourself.

If you think the case has been solved, please mark this thread as solved :
select Thread Tools from the menu link above and mark this thread as SOLVED.

Regards