Join Tables

22strider

Active Member
Joined
Jun 11, 2007
Messages
299
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 />
 

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
299
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
299
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
299
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
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
299
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
 

Forum statistics

Threads
1,081,852
Messages
5,361,694
Members
400,648
Latest member
mamamia93

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top