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