Embedded Files
Joining Tables is a database operation however it is possible to do this in Excel.
There's a few methods:
Fuzzy Lookup Merge Tables
Custom Macro without database commands
Custom Macro with database command
PowerPivot
Pivot Table Relationships
Probably the most point-click method is Fuzzy Lookup.
Fuzzy Lookup
Fuzzy Lookup
Install the Fuzzy Lookup add-in
Open up your Excel Spreadsheet with the 2 ranges you want to join.
Convert both ranges into tables:
4. If you want to merge all the entries from both tables, there are two methods.
in the first table, add the entire FRN column from the Table 2 below the last row of the first table:
A. Put new orphans at the end
A. Put new orphans at the end
4.A.1. In the first table, add the entire FRN column from the Table 2 below the last row of the first table:
4.A.2. If you don't want duplicates, then de-dupe the data: Run Data > Remove Duplicates on the FRNs of Table 1 to remove any duplicates. This means that Table 1 has all the FRNs with no duplicates. Any unique FRNs from Table 2 have now been added to the end (although their data is blank for now):
B. Keep new orphans as insertions
B. Keep new orphans as insertions
4.B.1. Extract the ID column from the first table.
4.B.2. Copy the ID column into Word as plain text (not a table). Save the Word document as "first table".
4.B.3. Extract the ID column from the second table.
4.B.4. Copy the ID column into Word as plain text (not a table). Save the Word document as "second table".
4.B.5. In Word, Review > Compare > Combine:
4.B.6. Follow the instructions in Word to combine both IDs into a single column.
4.B.7. Review the combined document and reject any deletions to merge the IDs into a single table.
4.B.8. Use this merged ID as the left hand table for both sets of data.
5. In Sheet 3, click on the first cell. This will be where Fuzzy Lookup outputs its analysis. This step is very important – if you forget you'll overwrite all your data!! If this happens then just click the "UNDO" button at the bottom of the Fuzzy Lookup pane.
6. In Fuzzy Lookup > Fuzzy Lookup, remove the Match Columns as this default match is probably going to be wrong.
7. Select both tables. Match only the ID (click on the middle icon that looks like a spider web). Set the Similarity Threshold to 1 (exact match). Leave all the Output Columns ticked or you'll be missing data after the matching.
8. If the right hand table has duplicates, choose the number of matches so that all of them will appear.
9. Press "Go".
10. Review the data output to make sure that the lines are matched properly, especially the unique Table 2 lines.
If the Fuzzy Match reports any strange errors, try replacing all Carriage Returns with another character.
Custom Macro without database commands
Custom Macro without database commands
To make this macro work, you need to first activate the add-in: Tools > References > Microsoft Scripting Runtime
http://blog.theroyweb.com/table-join-operation-in-excel
Sub TestRun()
'
' TestRun Macro
' Macro recorded 8/16/2009 by Rudrava Roy
'
JoinTables Worksheets("Sheet1").Range("A3:C17"), _
Worksheets("Sheet1").Range("E3:G17"), "Movie"
End Sub
Public Sub JoinTables(rngTable1 As Range, rngTable2 As Range, _
strJoinField As String)
' Existing tables passed as range arguments
' Field names (headers) to join on passed as strings
' First row must be table headers
' Measure length of both tables choose long table (A) and short table (B)
Dim rngA As Range, rngB As Range
If rngTable1.Rows.Count >= rngTable2.Rows.Count Then
Set rngA = rngTable1
Set rngB = rngTable2
Else
Set rngA = rngTable2
Set rngB = rngTable1
End If
' Create new sheet with headers from longer table (A)
' followed by shorter table (B)
Dim newSheet As Worksheet
Set newSheet = Worksheets.Add
newSheet.Name = "Joined Table"
rngA.Rows(1).Copy (newSheet.Cells(1))
rngB.Rows(1).Copy (newSheet.Cells(1, rngA.Columns.Count + 1))
' Locate join columns in both tables
Dim cellB As Range
Dim cellA As Range
Dim rngJoinColB As Range
Dim tableAJoinCol As Integer
For Each cellA In rngA.Rows(1).Columns
If cellA.Value = strJoinField Then
tableAJoinCol = cellA.Column - rngA.Columns(1).Column + 1
End If
Next
For Each cellB In rngB.Rows(1).Columns
If cellB.Value = strJoinField Then
Set rngJoinColB = rngB.Columns( _
cellB.Column - rngB.Columns(1).Column + 1)
End If
Next
' Resize join column to exclude header row
Set rngJoinColB = rngJoinColB.Offset(1, 0).Resize( _
rngJoinColB.Rows.Count - 1, _
rngJoinColB.Columns.Count)
' Create dictionary of table B on join column
Dim dictTableB As Dictionary
Set dictTableB = New Dictionary
Dim rngTmp As Range
For Each cellB In rngJoinColB.Rows
Set rngTmp = rngB.Rows(cellB.Row - rngB.Rows(1).Row + 1)
Debug.Print (cellB.Address)
dictTableB.Add Trim(cellB.Value), rngTmp
Next
' Resize ranges to exclude header rows
Set rngA = rngA.Offset(1, 0).Resize(rngA.Rows.Count - 1, _
rngA.Columns.Count)
Set rngB = rngB.Offset(1, 0).Resize(rngB.Rows.Count - 1, _
rngB.Columns.Count)
' Iterate through each row in A
Dim i As Integer
i = 2 ' row to start inserting at
For Each cellA In rngA.Rows
' Copy row to new table (J) as new row
cellA.Copy newSheet.Cells(i, 1)
' If join column of row from A has dictionary match,
' copy row from B & mark row 'done'
If dictTableB.Exists(cellA.Columns(tableAJoinCol).Value) Then
dictTableB.Item(cellA.Columns(tableAJoinCol).Value).Copy _
newSheet.Cells(i, rngA.Columns.Count + 1)
dictTableB.Remove (cellA.Columns(tableAJoinCol).Value)
End If
i = i + 1
Next
' Iterate through each remaining row in B
Dim tmpRow
For Each tmpRow In dictTableB.Items
' Copy row to new row in J
tmpRow.Copy newSheet.Cells(i, rngA.Columns.Count + 1)
i = i + 1
Next
' Resize all columns in new sheet
For i = 1 To (rngA.Columns.Count + rngB.Columns.Count)
newSheet.Columns(i).EntireColumn.AutoFit
Next
End Sub
Page updated
Google Sites
Report abuse