0

I have a main big table. Of which I want column

  • Name
  • Value 1
  • Value 2
  • Flag 1
  • Flag 2

Flag 1 if Value 1 > Value 2, Flag 2 if Value 1 = 0.45. Obviously there are above 5K+ unique records of each. I was wondering if I could store above as collection and the add it to a dictionary with name as the key and collection as item?

Thanks

Varun Yadav
  • 15
  • 1
  • 4

1 Answers1

1

In the collection, you may use any object as an item. Thus, you may put Array, Object or Collection there. In these, you may put multiple values. Here you go with Array as Item. Run it and check the results in the immediate window.

Option Explicit

Public Sub TestMe()

    Dim myCol   As New Collection
    Dim myVar   As Variant        

    myCol.Add Array("A", "B"), "2"
    myCol.Add Array("C", "D"), "3"
    myCol.Add Array("F", "G", "H"), "6"

    For Each myVar In myCol
        Debug.Print myVar(LBound(myVar))
        Debug.Print myVar(UBound(myVar))
        Debug.Print "-------------------"
    Next myVar

End Sub

Here is the solution with a Scripting .Dictionary:

Option Explicit

Public Sub TestMe()

    Dim myCol   As Object
    Dim myVar   As Variant

    Set myCol = CreateObject("Scripting.Dictionary")    

    myCol.Add 2, Array("A", "B")
    myCol.Add 3, Array("C", "D")
    myCol.Add 6, Array("F", "G", "H")

    For Each myVar In myCol.Keys
        Debug.Print myCol(myVar)(LBound(myCol(myVar)))
        Debug.Print myCol(myVar)(UBound(myCol(myVar)))
        Debug.Print "-------------------"
    Next myVar

End Sub

This is the result in the immediate window:

A
B
-------------------
C
D
-------------------
F
H
-------------------
Vityata
  • 42,633
  • 8
  • 55
  • 100
  • The reason I want to combine that with dictionary is because I would have duplicate rows e.g. multiple 2s in your e.g. So was hoping the use of exists feature of dictionary – Varun Yadav Jul 17 '17 at 13:08
  • You can change the Collection object to a dictionary object, the logic is quite the same. @VarunYadav – Vityata Jul 17 '17 at 13:09
  • 1
    When using a dictionary, note you need to pull the array out of the dictionary if you want to edit it: https://stackoverflow.com/questions/26390638/vba-dictionary-with-dynamic-arrays/26390929#26390929 – Tim Williams Jul 17 '17 at 16:05