Copy non-contiguous columns into into a multi-dimension array?

Question

I'm trying to copy values from one sheet to another, comparing the Key values (columns A & C) and either pasting a value (column E) into the target sheet or adding a row and pasting all three values into A, C, F.

Here's a sample of the data:

SOURCE TABLE
A       B       C       D       E   
Name    Ext     Dept    Days    w22Hrs
------- ------- ------- ------- -------
Alan    x101    Level1  MTWTF   8
Brian   x102    Level1  MTWTF   30
Claire  x103    Level1  MTWTF   40
Denise  x104    Level2  MTWTF   16
Denise  x105    Level1  MTWTF   24

TARGET TABLE
A       B       C       D       E       F
Name    Ext     Dept    Days    w21Hrs  w22Hrs
------- ------- ------- ------- ------- -------
Brian   x102    Level1  MTWTF   32      
Denise  x104    Level2  MTWTF   16      
Denise  x105    Level1  MTWTF   8       
Eric    x106    Level1  MTWTF   36      

DESIRED RESULT
A       B       C       D       E       F
Name    Ext     Dept    Days    w21Hrs  w22Hrs
------- ------- ------- ------- ------- -------
Alan            Level1          0       8
Brian   x102    Level1  MTWTF   32      30
Claire          Level1          0       40
Denise  x104    Level2  MTWTF   16      16
Denise  x105    Level1  MTWTF   8       24
Eric    x106    Level1  MTWTF   36      0

I tried to copy the source data into an array using this code:

set rng = union(range("A2:A6"), range("C2:C6"), range("E2:E6"))
arrTemp = rng.value2
arr = application.transpose(arrTemp)

But all I get is are values from A2:A6. However this works:

set rng = range("A2:E6")
arrTemp = rng.value2
arr = application.transpose(arrTemp)

1 - Is there no easy way to put only the columns I want into the array? (Iterating through cell areas seems inelegant to me.)

2 - Is there an easier way to accomplish the overall goal of updating the target sheet? (Keep in mind I want to update w##Hrs for existing rows AND add new rows when needed.) Or are arrays my best bet? (Would a Collection be better?)

If it makes things easier, I can paste A:D into the target, but source.E still needs to go into target.F.

Thanks!

Pick up the whole input range into an array and just ignore the parts you don't need. — Tim Williams, Jul 20 '16 at 23:18
this seems like it will be easier with PivotTable/PowerPivot or Power Query — Slai, Jul 21 '16 at 00:50

score 0 · Accepted Answer · answered Jul 20 '16 at 23:27

A collection would work but I prefer to use a Scripting Dictionary. Scripting Dictionaries have an Exists method that you can use to see if a Key exists already, collections don't. When adding Keys to a collection you'll have to escape any errors caused by trying to add a duplicate key.

Sub UpdateTargetTable()
    Dim k As String
    Dim lastRow As Long, x As Long
    Dim dict As Object
    Dim arr

    Set dict = CreateObject("Scripting.Dictionary")

    With Worksheets("Source")
        lastRow = .Range("A" & Rows.Count).End(xlUp).Row

        For x = 2 To lastRow
            k = .Cells(x, 1) & .Cells(x, 2)

            If Not dict.Exists(k) Then
                dict.Add k, .Range(.Cells(x, 3), .Cells(x, 5)).Value
            End If

        Next

    End With

    With Worksheets("Target")
        lastRow = .Range("A" & Rows.Count).End(xlUp).Row

        For x = 2 To lastRow
            k = .Cells(x, 1) & .Cells(x, 2)

            If dict.Exists(k) Then
                arr = dict(k)

               .Cells(x, 3) = arr(1, 1)
               .Cells(x, 4) = arr(1, 2)
               .Cells(x, 6) = arr(1, 3)

            End If

        Next

    End With

End Sub

I didn't think a dictionary would work well for this problem because of the inability to sort by key, but I found a script to do that, making a dictionary the perfect fit. I did some more tweaking which I plan to post later when time permits. — MJA, Jul 27 '16 at 20:36
You could use a SortedList instead of a dictionary. Like a Dictionary, SortedList store data in key value pairs. The SortedList automatically sorts the keys as you add them. This article [SortedList](http://www.robvanderwoude.com/vbstech_data_sortedlist.php) , demonstrates how to use them. — , Jul 28 '16 at 03:13

Copy non-contiguous columns into into a multi-dimension array?

1 Answers1