Join Tables

22strider

Active Member
Joined
Jun 11, 2007
Messages
302
Hello Friends

Could you please help me with VBA Code for joining two tables on a common column and then generate a third table. Both the tables are on a separate worksheet and the result table needs to be on a separate sheet as well. I can easily do this using Access but my requirement is to use Excel. I am using Excel 2007.
Following are the tabls showing sample data; Table1 and Table2 are the source tables having Manufacturer P/N as the common column. And Result Table is showing how the joined table should look like.
Thanks for your help
Rajesh


<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">Table1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">P/N</td><td style=";">Manufacturer P/N</td><td style=";">Manufacturer Name</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">500-123</td><td style=";">700-ABC</td><td style=";">ABC</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">500-123</td><td style=";">700-DEF</td><td style=";">DEF</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">500-546</td><td style=";">600-XYZ</td><td style=";">XYZ</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">500-546</td><td style=";">600-PQR</td><td style=";">PQR</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">400-123</td><td style=";">700-ABC</td><td style=";">ABC</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style=";">Table2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Manufacturer P/N</td><td style=";">Manufacturer Name</td><td style=";">Country</td><td style=";">Type</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">700-ABC</td><td style=";">ABC</td><td style=";">USA</td><td style=";">Supplier</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">700-DEF</td><td style=";">DEF</td><td style=";">China</td><td style=";">Manufact.</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">600-XYZ</td><td style=";">XYZ</td><td style=";">India</td><td style=";">Supplier</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">600-PQR</td><td style=";">PQR</td><td style=";">USA</td><td style=";">Designer</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;"></td><td style=";">Result Table</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">P/N</td><td style=";">Manufacturer P/N</td><td style=";">Manufacturer Name</td><td style=";">Address</td><td style=";">Type</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";">500-123</td><td style=";">700-ABC</td><td style=";">ABC</td><td style=";">USA</td><td style=";">Supplier</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style=";">500-123</td><td style=";">700-DEF</td><td style=";">DEF</td><td style=";">China</td><td style=";">Manufact.</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style=";">500-546</td><td style=";">600-XYZ</td><td style=";">XYZ</td><td style=";">India</td><td style=";">Supplier</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style=";">500-546</td><td style=";">600-PQR</td><td style=";">PQR</td><td style=";">USA</td><td style=";">Designer</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style=";">400-123</td><td style=";">700-ABC</td><td style=";">ABC</td><td style=";">USA</td><td style=";">Supplier</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

RonEmm

Board Regular
Joined
Aug 18, 2009
Messages
108
Rajesh, below my code to join the tables. I made the assumption that table1 is the basis for table3. I used the sheet names "Table1", "Table2" and "Table3" just for this example.

Code:
Sub JoinTables()
Dim Lr As Long, i As Long
    Lr = Sheets("Table1").Cells(Rows.Count, 1).End(xlUp).Row
    Sheets("Table1").UsedRange.Resize(Lr - 1).Offset(1).Copy Destination:=Sheets("Table3").Cells(2, 1)
    With Sheets("Table3")
        For i = 2 To Lr
            .Cells(i, 4) = Sheets("Table2").Columns(2).Find(what:=Cells(i, 2)).Offset(, 2)
            .Cells(i, 5) = Sheets("Table2").Columns(2).Find(what:=Cells(i, 2)).Offset(, 3)
        Next
    End With
End Sub
 

22strider

Active Member
Joined
Jun 11, 2007
Messages
302
Hello RonEmm

After running your code I am getting Run-time error message "Object variable or with block variable not set and the following code line is highlighted:

Code:
.Cells(i, 4) = Sheets("Table2").Columns(2).Find(what:=Cells(i, 2)).Offset(, 2)
Could you please review and let me know any change that might be required?

Thanks
Rajesh
 

22strider

Active Member
Joined
Jun 11, 2007
Messages
302

ADVERTISEMENT

Hello ExcelTheCell

Thanks for your message. Do you have nay idea of making it dynamic? In the template I really do not know how many rows there will be in the source tables (Table1 and 2). Is there way that the users won't have to copy and paste rows on Result sheet for getting the the values?

Thanks
Rajesh
 

22strider

Active Member
Joined
Jun 11, 2007
Messages
302
Hello ExcelTheCell

I have come up with following workaround for making the result sheet dynamic.

I have added VBA code for copying first row of the result sheet (that contains linking and VLOOKUP formulae) and pasting in the number of rows equal to the rows in the first sheet. And making this code run anytime Sheet1 is changed.

Please let me know if you have some other solution in mind.

Thanks
Rajesh
 

RonEmm

Board Regular
Joined
Aug 18, 2009
Messages
108

ADVERTISEMENT

Rajesh, I don't know where I went wrong earlier today but this should do it.


Code:
Sub JoinTables()
Dim Lr As Long, i As Long
    Lr = Sheets("Table1").Cells(Rows.Count, 1).End(xlUp).Row
    Sheets("Table1").UsedRange.Resize(Lr - 1).Offset(1).Copy Destination:=Sheets("Table3").Cells(2, 1)
    With Sheets("Table3")
        For i = 2 To Lr
            .Cells(i, 4) = Sheets("Table2").Columns(1).Find(what:=.Cells(i, 2)).Offset(, 2)
            .Cells(i, 5) = Sheets("Table2").Columns(1).Find(what:=.Cells(i, 2)).Offset(, 3)
        Next
    End With
End Sub
 

22strider

Active Member
Joined
Jun 11, 2007
Messages
302
Hello RonEmm

Thanks for your message; unfortunately the code gives same error that I reported earlier. Does it run properly on your side? It would be very helpful if we can get this running.

Thanks
Rajesh
 

ExcelTheCell

Board Regular
Joined
Nov 14, 2010
Messages
158
You can use this code:

Code:
Sub Joiner()

'Select data from first worksheet
Worksheets(1).Select
Worksheets(1).Range(Columns(1), Columns(3)).Select
Selection.Copy

'Place that data in 3th sheet and overwrite existing data
Worksheets(3).Select
Worksheets(3).Range(Columns(1), Columns(3)).Select
ActiveSheet.Paste

'Find out how many rows is filled in first column
HowManyRows = WorksheetFunction.CountA(Columns(1))

'Insert formula under adress and type
Worksheets(3).Range("D2", "D" & HowManyRows).Select
Selection.FormulaR1C1 = "=VLOOKUP(RC[-2],Table2!R1C1:R5C4,3,FALSE)"
Worksheets(3).Range("E2", "E" & HowManyRows).Select
Selection.FormulaR1C1 = "=VLOOKUP(RC[-3],Table2!R1C1:R5C4,4,FALSE)"
Worksheets(3).Range("A1").Select

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,132,686
Messages
5,654,754
Members
418,151
Latest member
shukoor

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top