Creating a dictionary based on some input format?

[jxb]

To all

As part of macro (to post process data), I am exploring the ideas of getting rid of a vlookup() creation by something that could potentially be more flexible (long term)

With the following data held in 1 tab

What would be the best way of creating a dictionary which would look like this shown on the right?

The thinking is that with a dictionary I can check the keys (cannot have duplicated) and I can loop over a range of cells in a column (later in the macro) and pick up the item (from the dico) easily (to write the value in a cell).

Will post some basic code I am testing shortly

Thanks in advance
John

 

[jxb]

works as I want

Dim d           'Create a dictionary
Set d = CreateObject("Scripting.Dictionary")

'if string looks like <2:5,7,10:12>
 'remove 1st and last characters
 'split at each comma. one gets a string array - block of data
 'process each block - check if data as a colon
 'if yes then split (get block 2)
 
If InStr(asdatablock(i), sdelimcolon) <> 0 Then
    asdatablock2 = Split(asdatablock(i), sdelimcolon)
            
    ivalue = CInt(asdatablock2(0))
            
    While ivalue < CInt(asdatablock2(UBound(asdatablock2))) + 1
        If d.Exists(ivalue) = False Then
            d.Add ivalue, ipartname
        Else
            'Add Error
        End If
        ivalue = ivalue + 1
    Wend
            
Else
 'check and add value to dictionary as before
End If