VBA to join tables with unique key in first column

Tybaltcap · Apr 10th 2015

Hi,

I am trying to write a VBA script which will copy over a dynamic number of rows and columns from one table to another table based on a unique key in the first column of each table

I have attached an example file which has the two tables I would like to join. The 'Data Sheet' would start with just the three columns (unique key, manager, and role) and only ever have 3 columns. The 'merge from sheet' has the unique key, and more info about the employee (there is not a set number of columns here, but it will always be bookended by the unique key columns). There is also a matrix on that sheet which I do not want to include in the merge.

[TABLE="width: 395"]

[tr]

[td][/td]

[TD="colspan: 4"]Add to manager_info table[/TD]

[/tr]

[tr]

[TD="align: center"]Unique Key[/TD]
[TD="align: center"]Employee[/TD]
[TD="align: center"]Salary[/TD]
[TD="align: center"]Number[/TD]
[TD="align: center"]Hire Date[/TD]

[/tr]

[tr]

[TD="align: center"]1[/TD]
[TD="align: center"]Bobby[/TD]
[TD="align: center"]$10[/TD]
[TD="align: center"]3234[/TD]
[TD="align: center"]1/1/2014[/TD]

[/tr]

[tr]

[TD="align: center"]2[/TD]
[TD="align: center"]Rockey[/TD]
[TD="align: center"]$11[/TD]
[TD="align: center"]3245[/TD]
[TD="align: center"]2/2/2014[/TD]

[/tr]

[tr]

[TD="align: center"]3[/TD]
[TD="align: center"]Tommy[/TD]
[TD="align: center"]$10[/TD]
[TD="align: center"]3256[/TD]
[TD="align: center"]2/4/2014[/TD]

[/tr]

[tr]

[TD="align: center"]4[/TD]
[TD="align: center"]Billy[/TD]
[TD="align: center"]$20[/TD]
[TD="align: center"]3267[/TD]
[TD="align: center"]4/5/2015[/TD]

[/tr]

[tr]

[TD="align: center"]5[/TD]
[TD="align: center"]Mindy[/TD]
[TD="align: center"]$100[/TD]
[TD="align: center"]3278[/TD]
[TD="align: center"]10/10/1950[/TD]

[/tr]

[/TABLE]

Data Sheet: [TABLE="width: 500"]

[tr]

[TD="colspan: 3"] Before Macro is Run
[/TD]
[TD="colspan: 4"]Added from Macro
[/TD]

[/tr]

[tr]

[td]

Unique Key

[/td]

[td]

Manager

[/td]

[td]

Role

[/td]

[td]

Employee

[/td]

[td]

Salary

[/td]

[td]

Number

[/td]

[td]

Hire Date

[/td]

[/tr]

[tr]

[TD="align: right"]1
[/TD]

[td]

Bill

[/td]

[td]

Builder

[/td]

[td]

Bobby

[/td]

[TD="align: right"]$10
[/TD]
[TD="align: right"]3234
[/TD]
[TD="align: right"]1/1/2014
[/TD]

[/tr]

[tr]

[TD="align: right"]1
[/TD]

[td]

Bill

[/td]

[td]

Fixer

[/td]

[td]

Bobby

[/td]

[TD="align: right"]$10
[/TD]
[TD="align: right"]3234
[/TD]
[TD="align: right"]1/1/2014
[/TD]

[/tr]

[tr]

[TD="align: right"]1
[/TD]

[td]

Bill

[/td]

[td]

Cleaner

[/td]

[td]

Bobby

[/td]

[TD="align: right"]$10
[/TD]
[TD="align: right"]3234
[/TD]
[TD="align: right"]1/1/2014
[/TD]

[/tr]

[tr]

[TD="align: right"]2
[/TD]

[td]

Ted

[/td]

[td]

Doer

[/td]

[td]

Rockey

[/td]

[TD="align: right"]$11
[/TD]
[TD="align: right"]3245
[/TD]
[TD="align: right"]2/2/2014
[/TD]

[/tr]

[tr]

[TD="align: right"]3
[/TD]

[td]

Loise

[/td]

[td]

Shopper

[/td]

[td]

Tommy

[/td]

[TD="align: right"]$10
[/TD]
[TD="align: right"]3256
[/TD]
[TD="align: right"]2/4/2014
[/TD]

[/tr]

[tr]

[TD="align: right"]3
[/TD]

[td]

Loise

[/td]

[td]

Planner

[/td]

[td]

Tommy

[/td]

[TD="align: right"]$10
[/TD]
[TD="align: right"]3256
[/TD]
[TD="align: right"]2/4/2014
[/TD]

[/tr]

[tr]

[TD="align: right"]3
[/TD]

[td]

Loise

[/td]

[td]

Fetcher

[/td]

[td]

Tommy

[/td]

[TD="align: right"]$10
[/TD]
[TD="align: right"]3256
[/TD]
[TD="align: right"]2/4/2014
[/TD]

[/tr]

[tr]

[TD="align: right"]4
[/TD]

[td]

Kate

[/td]

[td]

Driver

[/td]

[td]

Billy

[/td]

[TD="align: right"]$20
[/TD]
[TD="align: right"]3267
[/TD]
[TD="align: right"]4/5/2015
[/TD]

[/tr]

[tr]

[TD="align: right"]5
[/TD]

[td]

Rothgaur

[/td]

[td]

Security

[/td]

[td]

Mindy

[/td]

[TD="align: right"]$100
[/TD]
[TD="align: right"]3278
[/TD]
[TD="align: right"]10/10/1950
[/TD]

[/tr]

[/TABLE]

I have found and adapted some code which does a vlookup to bring just the first column. I thought about adapting with a loop to add each column row by row, but I think that would be very time consuming...a match, select and copy/paste may be faster/lighter? Maybe there is another way I don't know about (collections or something)?

Note: This will be part of a series of macros which convert the matrix (employees names linked to roles with the X) into a pivot table, the end goal being to have a list of Managers, their employees, and which roles they have.

Thank you so much for any help you can give!!

forum.ozgrid.com/index.php?attachment/64720/

Mumps · Apr 10th 2015

Re: VBA to join tables with unique key in first column

Try:

Code

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("data table").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim ID As Range
    Dim foundID As Range
    For Each ID In Sheets("data table").Range("A2:A" & LastRow)
        Set foundID = Sheets("merge from sheet").Range("A:A").Find(ID, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundID Is Nothing Then
            Sheets("merge from sheet").Range("B" & foundID.Row & ":E" & foundID.Row).Copy Sheets("data table").Range("D" & ID.Row)
        End If
    Next ID
    Application.ScreenUpdating = True
End Sub

Display More

Tybaltcap · Apr 15th 2015

Re: VBA to join tables with unique key in first column

Awesome! Thank you Mumps so much for the help!

I modified the code so that it was not hard coded to the copy columns where ColCount was defined as the last column of the merge from sheet range.

Code

Worksheets("merge from sheet").Cells(foundID.Row, 2).Resize(, ColCount).Copy Sheets("data table").Range("D" & ID.Row)

Mumps · Apr 15th 2015

Re: VBA to join tables with unique key in first column

My pleasure.

VBA to join tables with unique key in first column

Participate now!

Share