title: VBA Populate array from another array
body: <p>I am trying to get a Final array populated with unique keys with values from an existing 2 dimensional array using the following code:
  The final array has 3 dimensions for Data, I want the array to look like this:</p>
  
  <p>Finalarray(0): {a, 1, 4, 8} .... Finalarray(4): {e, Empty, Empty, 12}</p>
  
  <p>My code initialises the array above with the keys i.e. a,b,c,d,e but , I am not sure what the best general way of populating would be!</p>
  
  <p><strong>Assume that each "a" entry is a new row</strong></p>
  
  <p>My attempt that currently works (but VERY manual) is this just to pick out "e":</p>
  
  <pre><code>    Sub ArrayTest()
      Dim PreservedKeys As Variant
      Dim Data(0 To 2, 0 To 3) As Variant
      Dim rRef As Range
      Dim PreservedData As Variant
      Dim MergedArray As Variant
      Dim i As Integer
      Dim uniquePreservedKeys As Variant
  
      Dim FinalArray
      Dim Constant As Integer
  
      PreservedKeys = Array("a", "b", "c", "a", "b", "c", "d", "a", "b", "c", "d", "e")
      PreservedData = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
      Constant = 3
  
  
      ReDim MergedArray(0 To UBound(PreservedKeys), 0 To 1)
  
        For i = 0 To UBound(PreservedKeys)
  
          MergedArray(i, 0) = PreservedKeys(i)
          MergedArray(i, 1) = PreservedData(i)
  
        Next i
  
  
      uniquePreservedKeys = M_snb(PreservedKeys)
  
      ReDim FinalArray(0 To UBound(uniquePreservedKeys), 0 To Constant)
  
      For i = 0 To 4
          FinalArray(i, 0) = uniquePreservedKeys(i)
      Next i
  
  
  
      Set rRef = Application.Range("TestRange")
  
      rRef.Resize(UBound(Data, 1) + 1, UBound(Data, 2) + 1) = Data
  
      'MY ATTEMPT SO FAR --&gt; Very manual to just get the e entry
          If MergedArray(i, 0) = "a" Then
              counter = counter + 1
          End If
  
       If counter = 1 Then
         If MergedArray(i, 0) &lt;&gt; "e" Then
             FinalArray(4, counter) = ""
         Else
          FinalArray(4, counter) = MergedArray(i, 1)
         End If
       End If
  
        If counter = 3 Then
         If MergedArray(i, 0) &lt;&gt; "e" Then
             FinalArray(4, counter) = ""
         Else
          FinalArray(4, counter) = MergedArray(i, 1)
         End If
       End If
  
  
       Next i
  
  
      End Sub
  
  
   Function M_snb(UniqueKeys As Variant)
          With CreateObject("scripting.dictionary")
              For Each it In UniqueKeys
                  c10 = .Item(it)
              Next
              an = .keys ' the array .keys contains all unique keys
  
          End With
  
      M_snb = an
  
      End Function
  </code></pre>