Merge two Excel tables (using VBA)

Question

I have been trying to figure out how to merge two tables from the same workbook into a third one using VBA. Example :

Worksheet1:

From     To     Value
Italy    Japan  1000
France   Japan  500
Canada   Japan  0
France   Italy  700

Worksheet2:

From     To     Value
Italy    Japan  5555
France   Japan  1111
Canada   Japan  777
Canada   France 333

Disired output (worksheet3):

From     To     Value1  Value2
Italy    Japan  1000    5555
France   Japan  500     1111
Canada   Japan  0       777
France   Italy  700
Canada   France         333

I would need a VBA solution since the original tables are about 400 rows long, and I would need to perform the same operation for several workbooks. I would be very grateful for any suggestion regarding this problem !

Edit:

In case it is of interest to anyone, I managed to make a working code. Worksheet1 was a nickname for "List Import" and Worksheet2 is "List Export". In both sheets, I inserted a column (C) that states both countries. I used that new column and the values to build the table in Worksheet3 (now "Combolist").

Sub combolist()
    Dim lastRowImp As Long, lastRowExp As Long, startPaste As Long, endPaste As Long
    Dim ws As Worksheet, Lookup_Range As Range, i As Integer
    Dim lastRow As Long

    lastRowImp = Sheets("List Import").Cells(Rows.Count, 1).End(xlUp).Row
    lastRowExp = Sheets("List Export").Cells(Rows.Count, 1).End(xlUp).Row
    startPaste = lastRowImp + 1
    endPaste = lastRowImp + lastRowExp - 1

    'add a new sheet and headers
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Combolist"
    Sheets("Combolist").Range("B1") = "Import"
    Sheets("Combolist").Range("C1") = "Export"
    Sheets("Combolist").Range("C1").EntireRow.Font.Bold = True

    'copy flows from import and export list
    Sheets("Combolist").Range("A1:A" & lastRowImp) = Sheets("List Import").Range("C1:C" & lastRowImp).Value
    Sheets("Combolist").Range("A" & startPaste & ":A" & endPaste) = Sheets("List Export").Range("C2:C" & lastRowExp).Value

    'remove duplicates
    lastRow = Sheets("Combolist").Cells(Rows.Count, 1).End(xlUp).Row
    Sheets("Combolist").Range(Cells(1, 1), Cells(lastRow, 1)).RemoveDuplicates Columns:=Array(1), Header:=xlYes

    Set ws = ActiveWorkbook.Sheets("Combolist")
    lastRow = Sheets("Combolist").Cells(Rows.Count, 1).End(xlUp).Row

    'populate Import values
    Set Lookup_Range = Sheets("List Import").Range("C1:D" & lastRowImp)

    With ws
        For i = 2 To lastRow
            On Error Resume Next
                If Application.WorksheetFunction.VLookup(ws.Cells(i, 1), Lookup_Range, 2, False) = "" Then
                ws.Cells(i, 2) = 0
                Else
                ws.Cells(i, 2) = Application.WorksheetFunction.VLookup(ws.Cells(i, 1), Lookup_Range, 2, False)
                End If
        Next i
    End With

    'populate Export values
    Set Lookup_Range = Sheets("List Export").Range("C1:D" & lastRowExp)

    With ws
        For i = 2 To lastRow
            On Error Resume Next
                If Application.WorksheetFunction.VLookup(ws.Cells(i, 1), Lookup_Range, 2, False) = "" Then
                ws.Cells(i, 3) = 0
                Else
                ws.Cells(i, 3) = Application.WorksheetFunction.VLookup(ws.Cells(i, 1), Lookup_Range, 2, False)
                End If
        Next i
    End With

End Sub

Answer 1

While this could be solved with VBA, you're likely to be better off using formulas (unless you must do this very frequently). The VBA solution will require some know-how, and even more if you want to be able to maintain the solution.

An Excel formula would be quite simple. First, create a UniqueID column:

UniqueID        From     To     Value
Italy_Japan     Italy    Japan  1000  
France_Japan    France   Japan  500   
Canada_Japan    Canada   Japan  0     
France_Italy    France   Italy  700
Canada_France   Canada   France     

You would do the same thing for both tables. Next, get all of the unique UniqueID's. For this, you could use Data > Remove Duplicates, just be sure to make a copy before removing duplicates, otherwise you are removing records from your source. Put this list of UniqueID's into a new Table. Keep in mind that all of this will be easier if all of your data is in Table format (you'll see a Table tab in the ribbon when inside of the table range.

If you need to format your data as a table, go to the worksheet, press CTRL+HOME (this goes to the very first cell). If your first cell is in another location, just navigate there instead. If your table is the only data on the worksheet, try using CTRL+SHIFT+END from here to highlight to the last used cell. Otherwise, a combination of CTRL+SHIFT+RIGHT and CTRL+SHIFT+DOWN will get you what you need. Finally, name your table for the love of all that is excel, this one simple habit saves a ton of time. For my example I will assume that you have a Primary and Secondary table.

Our formula in our combined table would then look something like this:

=IfError(Vlookup([UniqueID], Primary, Column(Primary[Value]), False), "")

Or, if your Primary table doesn't start in the first column, use this:

=IfError(Vlookup([UniqueID], Primary, 4, False), "")

The difference here is that the former will change the index as the column is moved, the latter will not, and must be edited if the table is edited.

Do the same thing in your next column for the other table:

=IfError(Vlookup([UniqueID], Secondary, Column(Primary[Value]), False), "")

=IfError(Vlookup([UniqueID], Secondary, 4, False), "")

This will 'merge' the two sets based on the shared UniqueID and will leave blanks if the record doesn't exist. Learning how to do this may be less convenient than the learning how to do it in VBA, but I would strongly dissuade you from trying to learn VBA if you can't use an implementation like this.

To be clear, the reason why the formula approach is ideal in this instance is that the task you are asking for help with is very simple, and you would be better developing your Excel skills since, doing so, will allow you to solve similar tasks much faster in the future. Even a novice could implement this solution within 15 minutes or so, where it would easily take you days to learn a scalable VBA solution.