CODEWITHSUNDEEP
excelvbadictionary
Shan
Shan

Reputation: 439

VBA Dictionary of dictionaries?

I am creating unique collections from every column in my sheet under the column headings using dictionary object. Column headings are in Row 1.

Problem is my dictionaries contains unique items from previous columns. for example if call a dictionary of column 4, it contains all unique items and headings from columns 1,2,3. I need only unique items from that perticular column. Any idea how to correct this code?.

Sub cre_Dict()
Dim fulArr As Variant

Set d = CreateObject("scripting.dictionary")

With Sheets("Database")
        fulArr = .Range("A1:IO27") 'assign whole table to array
        For j = 1 To UBound(fulArr, 2) 'looping from 1st column to last column
            For i = 2 To UBound(fulArr, 1) 'looping from row2 to last row
                If Len(fulArr(i, j)) > 0 Then 'if not blank cell
                    d00 = d.Item(fulArr(i, j)) 'add to dictionary
                End If
            Next i
            d(fulArr(1, j)) = d.keys 'create dictionary under column heading
        Next j
End With

End Sub

Thanks

Upvotes: 1

Views: 3680

Answers (2)

omegastripes
omegastripes

Reputation: 12602

Consider the below example, based on your code with minor changes I have made:

Dim d As Object

Sub cre_Dict()
    Dim fulArr As Variant
    Dim q As Object
    Dim j As Long
    Dim i As Long

    Set d = CreateObject("Scripting.Dictionary")
    fulArr = Sheets("Database").Range("A1:IO27") 'assign whole table to array
    For j = 1 To UBound(fulArr, 2) 'looping from 1st column to last column
        Set q = CreateObject("Scripting.Dictionary")
        For i = 2 To UBound(fulArr, 1) 'looping from row2 to last row
            If Len(fulArr(i, j)) > 0 Then 'if not blank cell
                q(fulArr(i, j)) = Empty 'add to dictionary
            End If
        Next i
        d(fulArr(1, j)) = q.Keys 'create dictionary under column heading
    Next j

End Sub

Upvotes: 1

PaulG
PaulG

Reputation: 1189

If I've got this correct, you have duplicates in different columns. So basically your keys in the dictionary are not unique as you iterate over columns. If that's the case, when you add your keys to the dictionary, create a composite key. For example something like:

Dim sKey as string sKey = "A~" & "ABCD"

This will then make the key unique for column A. When processing the collection, you can strip out the "A~" part for each column.

Upvotes: 0

Related Questions