Compiling and accessing nested collection within a nested dictionary

jrevball

New Member
Joined
Mar 3, 2020
Messages
4
Office Version
365, 2019, 2016, 2013
Platform
Windows
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.

What I want to do:
Given a table of data which includes Part number, quote date, quantity, price and supplier, I want iterate through the part number column to evaluate the price at a given quantity range for a particular part number. If the price is lower than the current price, add it to a data set. When done cycling through the table, return the results to a summary page. Only data that has newer dates (ie, less than 6 months old) will be evaluated.

Given (hard-coded in macro for now):
Quantity Ranges
6 month age restriction for evaluating the data.

I thought about creating a dictionary for the pat numbers, then a nested dictionary for the quantity ranges. The item for the quantity range would be a collection of price, quote date, and supplier. While this seems logical in my head, it is another thing to compile in vba. I can't seem to access and/or modify the collection for a given part number at a given quantity range to replace a collection record (price, date, supplier) with new data.

Here is my code.

VBA Code:
Sub ReportLowestCostperRangeQty()
'if the cost point is from the same supplier, use the most current date for cost @ qty
'if there are multiple suppliers at the same cost point, use lowest cost @ qty for a given time period (6 months)
Dim dataArr As Variant
Dim ws As Worksheet
Dim dataLo As ListObject
Dim targetArr As Variant
Dim qty(0 To 6)
Dim i, j, q, vend As Integer
Dim partDict As Dictionary
Dim qtyDict As Dictionary
Dim qtyStr As String
Dim coll As clsPrice
'Dim supplierDict As Dictionary ' not used
'Dim dateDict As Dictionary   ' not used

qty(0) = 1
qty(1) = 5
qty(2) = 10
qty(3) = 25
qty(4) = 50
qty(5) = 100
qty(6) = 250

Set ws = ActiveWorkbook.Worksheets("Data")
Set dataLo = ws.ListObjects("data")
Set partDict = New Dictionary

dataArr = dataLo.DataBodyRange
ReDim targetArr(1 To 5, 1 To 1)
For i = 1 To UBound(dataArr, 1)
  Select Case dataArr(i, 6)
    Case qty(0) To qty(1)
      qtyStr = qty(0) & " - " & qty(1)
    Case qty(1) + 1 To qty(2)
      qtyStr = qty(1) & " - " & qty(2)
    Case qty(2) + 1 To qty(3)
      qtyStr = qty(2) & " - " & qty(3)
    Case qty(3) + 1 To qty(4)
      qtyStr = qty(3) & " - " & qty(4)
    Case qty(4) + 1 To qty(5)
      qtyStr = qty(4) & " - " & qty(5)
    Case qty(5) + 1 To qty(6)
      qtyStr = qty(5) & " - " & qty(6)
    Case Is > qty(6)
      qtyStr = qty(6) & "+"
  End Select
  If CDate(dataArr(i, 2)) > CDate(DateAdd("m", -6, Date)) Then
    If Not partDict.Exists(dataArr(i, 1)) Then
      Call partDict.Add(dataArr(i, 1), New Scripting.Dictionary)
      Set qtyDict = New Dictionary
      Set coll = New clsPrice
      coll.price = dataArr(i, 7)
      coll.qdate = dataArr(i, 4)
      coll.supplier = dataArr(i, 8)

      qtyDict.Add qtyStr, coll
    Else
      If Not qtyDict.Exists(qtyStr) Then
        Set coll = New clsPrice
        coll.price = dataArr(i, 7)
        coll.qdate = dataArr(i, 4)
        coll.supplier = dataArr(i, 8)
        qtyDict.Add qtyStr, coll
      Else
        If coll.price >= dataArr(i, 7) Then
          coll.price = dataArr(i, 7)
          coll.qdate = dataArr(i, 4)
          coll.supplier = dataArr(i, 8)
          Set qtyDict(qtyStr) = coll
        End If
      End If
    End If

    Set partDict(dataArr(i, 1)) = qtyDict
  End If
Next i

' will be a clean up section to set all objects to nothing before ending.

End Sub
and the class clsPrice has the following variables:
VBA Code:
Public supplier As String
Public qdate As Date
Public price As Currency
Thanks for any help or guidance on this!
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

jrevball

New Member
Joined
Mar 3, 2020
Messages
4
Office Version
365, 2019, 2016, 2013
Platform
Windows
Sorry, I couldn't figure out a way to edit my initial post.
I've modified and cleaned up the code a bit, adding comments and removing variables I wasn't using.

VBA Code:
Sub ReportLowestCostperRangeQty()
'if the cost point is from the same supplier, use the most current date for cost @ qty
'if there are multiple suppliers at the same cost point, use lowest cost @ qty for a given time period (6 months)
Dim dataArr As Variant
Dim ws As Worksheet
Dim dataLo As ListObject
Dim qty(0 To 6)
Dim i, j, q As Integer
Dim partDict As Dictionary
Dim qtyDict As Dictionary
Dim qtyStr As String
Dim coll As clsPrice

Set ws = ActiveWorkbook.Worksheets("Data")
Set dataLo = ws.ListObjects("data")
Set partDict = New Dictionary

'hardcode qty break points for creation of qtyStr
qty(0) = 1
qty(1) = 5
qty(2) = 10
qty(3) = 25
qty(4) = 50
qty(5) = 100
qty(6) = 250

'dataArr(i,1) = part number
'dataArr(i,3) = quote date
'dataArr(i,6) = quote quantity
'dataArr(i,7) = price
'dataArr(i,8) = supplier

'pull the datarange into an array
dataArr = dataLo.DataBodyRange

For i = 1 To UBound(dataArr, 1)
'create the quantity str qtyStr based on quote quantity
  Select Case dataArr(i, 6)
    Case qty(0) To qty(1)
      qtyStr = qty(0) & " - " & qty(1)
    Case qty(1) + 1 To qty(2)
      qtyStr = qty(1) & " - " & qty(2)
    Case qty(2) + 1 To qty(3)
      qtyStr = qty(2) & " - " & qty(3)
    Case qty(3) + 1 To qty(4)
      qtyStr = qty(3) & " - " & qty(4)
    Case qty(4) + 1 To qty(5)
      qtyStr = qty(4) & " - " & qty(5)
    Case qty(5) + 1 To qty(6)
      qtyStr = qty(5) & " - " & qty(6)
    Case Is > qty(6)
      qtyStr = qty(6) & "+"
  End Select
  
'check to see if the quote date is within the 6 month window
  If CDate(dataArr(i, 3)) > CDate(DateAdd("m", -6, Date)) Then

'check to see if the part number exists in the part Dictionary.  if it doesn't add it and set it's sub dictionaries as new
    If Not partDict.Exists(dataArr(i, 1)) Then
      Call partDict.Add(dataArr(i, 1), New Scripting.Dictionary)
      Set qtyDict = New Dictionary
      Set coll = New clsPrice
      coll.price = dataArr(i, 7)
      coll.qdate = dataArr(i, 4)
      coll.supplier = dataArr(i, 8)
      qtyDict.Add qtyStr, coll
      Set partDict(dataArr(i, 1)) = qtyDict

'the part number already exists, check to see if the range qty exists
    ElseIf Not partDict(dataArr(i, 1)).Exists(qtyDict) Then
      Set coll = New clsPrice
      coll.price = dataArr(i, 7)
      coll.qdate = dataArr(i, 4)
      coll.supplier = dataArr(i, 8)
      Call partDict(dataArr(i, 1)).Add(qtyDict, coll)
      
'the part number exists, qty range exists, now check to see if the price is lower than the current price being evaluated.
'replace higher price with lower price
    Else
      If coll.price >= dataArr(i, 7) Then
        coll.price = dataArr(i, 7)
        coll.qdate = dataArr(i, 4)
        coll.supplier = dataArr(i, 8)
        Set partDict(dataArr(i, 1))(qtyDict) = coll
      End If
    End If
  End If
Next i
'following is a debug tool that isn't working at the moment.
TraverseDictionary partDict

'cleanup objects
Set ws = Nothing
Set dataLo = Nothing
Set partDict = Nothing
Set qtyDict = Nothing
Set coll = Nothing


End Sub
 

jrevball

New Member
Joined
Mar 3, 2020
Messages
4
Office Version
365, 2019, 2016, 2013
Platform
Windows
A question came up asking what do I want to do with this data.

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 would host part numbers, with the item being quantity ranges - also I'm presuming as a dictionary. Within the nested dictionary, there will be a collection of the three other variables (date, price, supplier).

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 between part 1 and part 2 below)
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 this:
Part number [DICT1]Qty range [DICT2]Price [Coll.price]Supplier[Coll.supplier]Quote Date [coll.date]
Part 11 to 5
$124.94​
Supplier 2
11/16/2018​
6 to 10
$71.46​
Supplier 2
11/16/2018​
11 to 25
$52.08​
Supplier 2
11/16/2018​
51 to 100
$10.49​
Supplier 1
9/12/2018​
101 to 250
$8.85​
Supplier 1
9/12/2018​
Part 226 to 50
$58.32​
Supplier 3
10/5/2018​
51 to 100
$52.74​
Supplier 3
10/5/2018​
101 to 250
$49.41​
Supplier 3
10/5/2018​
Part 326 to 50
$45.88​
Supplier 3
10/5/2018​
51 to 100
$37.23​
Supplier 3
10/5/2018​
101 to 250
$32.02​
Supplier 3
10/5/2018​
Part 426 to 50
$18.51​
Supplier 3
10/5/2018​
51 to 100
$14.28​
Supplier 3
10/5/2018​
101 to 250
$11.76​
Supplier 3
10/5/2018​
Part 551 to 100
$18.84​
Supplier 3
10/5/2018​
101 to 250
$15.60​
Supplier 3
10/5/2018​
250+
$13.28​
Supplier 3
10/5/2018​
Part 626 to 50
$80.53​
Supplier 3
11/7/2018​
51 to 100
$60.23​
Supplier 3
11/7/2018​
101 to 250
$52.83​
Supplier 3
11/7/2018​
 

Watch MrExcel Video

Forum statistics

Threads
1,099,569
Messages
5,469,462
Members
406,653
Latest member
CBeeker

This Week's Hot Topics

Top