Join Tables

22strider

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



Excel 2007
ABCDE
1Table1
2P/NManufacturer P/NManufacturer Name
3500-123700-ABCABC
4500-123700-DEFDEF
5500-546600-XYZXYZ
6500-546600-PQRPQR
7400-123700-ABCABC
8
9
10Table2
11Manufacturer P/NManufacturer NameCountryType
12700-ABCABCUSASupplier
13700-DEFDEFChinaManufact.
14600-XYZXYZIndiaSupplier
15600-PQRPQRUSADesigner
16
17
18
19Result Table
20P/NManufacturer P/NManufacturer NameAddressType
21500-123700-ABCABCUSASupplier
22500-123700-DEFDEFChinaManufact.
23500-546600-XYZXYZIndiaSupplier
24500-546600-PQRPQRUSADesigner
25400-123700-ABCABCUSASupplier
26
Sheet1
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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
Back
Top