Excel: Joining Tables

Joining Tables is a database operation however it is possible to do this in Excel.

There's a few methods:

Probably the most point-click method is Fuzzy Lookup.

Fuzzy Lookup

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

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

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

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