Creating multiple columns from multiple columns and passing the arrays to other subroutines!

[KolGuyXcel]

I'm pretty new to excel vba macro coding! So, not sure, if I'm putting my problem clearly enough!

I'm working on a data of thousands of rows and around a dozen columns. Please note there's no unique value column, i.e. even though each column contains a unique set of values, irrespective of the unique set of values appearing in other columns, all the columns have repetition of some or all the unique values appearing in the column. I'm focused right now only on the Columns B and C! I'm looking to create an array out of only the unique values in column B, which for the sake of brevity, let's call ColBArray. And for each unique array member in ColBArray a "sub-array" of the corresponding unique values in Column C! So, if there are 500 unique values in column B, this should create 500 sub-arrays, but only of the corresponding unique values from column C.

Also, note that I would need to call ColBArray from other subroutines. And once any member of ColBArray is called upon in the subroutine the corresponding "sub-array" of the unique column C values array should also initialized within that subroutine.

 

[Fluff]

How about

[COLOR=#ff0000]Option Explicit
Public ColBdic As Object[/COLOR]
Sub GetUniques()
   Dim Ary As Variant
   Dim i As Long
   
   Set ColBdic = CreateObject("scripting.dictionary")
   Ary = Sheets("Pcode").Range("H2", Sheets("Pcode").Range("H" & Rows.Count).End(xlUp).Offset(, 1)).value2
   For i = 1 To UBound(Ary)
      If Not ColBdic.exists(Ary(i, 1)) Then
         ColBdic.add Ary(i, 1), CreateObject("scripting.dictionary")
         ColBdic(Ary(i, 1)).add Ary(i, 2), Nothing
      ElseIf Not ColBdic(Ary(i, 1)).exists(Ary(i, 2)) Then
         ColBdic(Ary(i, 1)).add Ary(i, 2), Nothing
      End If
   Next i
End Sub

And then you can use that like

Sub t1()
   Dim Ky As Variant
   Dim i As Long
   
   With Sheets("sheet1")
      .Range("A1").Resize(, ColBdic.Count).Value = ColBdic.keys
      For Each Ky In ColBdic.keys
         i = i + 1
         .Cells(2, i).Resize(ColBdic(Ky).Count).Value = Application.Transpose(ColBdic(Ky).keys)
      Next Ky
   End With
   
End Sub

NOTE:
The 2 lines in red must go at the very top of a standard module, before any code.

 

[KolGuyXcel]

Re: Creating multiple arrays from multiple columns and passing the arrays to other subroutines!

How about

[COLOR=#ff0000]Option Explicit
Public ColBdic As Object[/COLOR]
Sub GetUniques()
   Dim Ary As Variant
   Dim i As Long
   
   Set ColBdic = CreateObject("scripting.dictionary")
   Ary = Sheets("Pcode").Range("H2", Sheets("Pcode").Range("H" & Rows.Count).End(xlUp).Offset(, 1)).value2
   For i = 1 To UBound(Ary)
      If Not ColBdic.exists(Ary(i, 1)) Then
         ColBdic.add Ary(i, 1), CreateObject("scripting.dictionary")
         ColBdic(Ary(i, 1)).add Ary(i, 2), Nothing
      ElseIf Not ColBdic(Ary(i, 1)).exists(Ary(i, 2)) Then
         ColBdic(Ary(i, 1)).add Ary(i, 2), Nothing
      End If
   Next i
End Sub

And then you can use that like

Sub t1()
   Dim Ky As Variant
   Dim i As Long
   
   With Sheets("sheet1")
      .Range("A1").Resize(, ColBdic.Count).Value = ColBdic.keys
      For Each Ky In ColBdic.keys
         i = i + 1
         .Cells(2, i).Resize(ColBdic(Ky).Count).Value = Application.Transpose(ColBdic(Ky).keys)
      Next Ky
   End With
   
End Sub

NOTE:
The 2 lines in red must go at the very top of a standard module, before any code.

The above code would only create the array of unique values in Column B, right? But will it create the corresponding sub-arrays of the unique values from column C?

 

[Fluff]

Re: Creating multiple arrays from multiple columns and passing the arrays to other subroutines!

But will it create the corresponding sub-arrays of the unique values from column C?

Yes

 

[KolGuyXcel]

Re: Creating multiple arrays from multiple columns and passing the arrays to other subroutines!

Ignore my earlier reply!

It's working fine! I missed the (, 2) in the original code :

Option Explicit
Public ColBdic As Object
Sub GetUniques()
Dim Ary As Variant
Dim i As Long

Set ColBdic = CreateObject("scripting.dictionary")
Ary = Sheets("Pcode").Range("H2", Sheets("Pcode").Range("H" & Rows.Count).End(xlUp).Offset(, 1)).value2
For i = 1 To UBound(Ary)
If Not ColBdic.exists(Ary(i, 1)) Then
ColBdic.add Ary(i, 1), CreateObject("scripting.dictionary")
ColBdic(Ary(i, 1)).add Ary(i, 2), Nothing
ElseIf Not ColBdic(Ary(i, 1)).exists(Ary(i, 2)) Then
ColBdic(Ary(i, 1)).add Ary(i, 2), Nothing
End If
Next i
End Sub



Thanks Fluff!

 

[Fluff]

Re: Creating multiple arrays from multiple columns and passing the arrays to other subroutines!

Glad to help & thanks for the feedback

 

[KolGuyXcel]

Re: Creating multiple arrays from multiple columns and passing the arrays to other subroutines!

Glad to help & thanks for the feedback

Hi Fluff (or others),

Sorry to bother you again!
I see that all the items in the nested sub-dictionary are set to "Nothing". I think, if I could use it to store a particular value, I might be able to save a couple of iterations in other macros.

What would be the best way to access(read) any particular item in the nested subdirectory?

 

[Fluff]

Re: Creating multiple arrays from multiple columns and passing the arrays to other subroutines!

Something like

      MsgBox ColBdic(Range("A1").Value)(Range("B1").Value)