使用公共列合并两个excel文件

I have two excel sheets. I have to merge the two such that the values in one match with the other. For eg.

我有两张excel床单。我必须将两者合并,使得一个中的值与另一个匹配。例如。

The first excel,    the 2nd excel

1  t                 1   tes1
2  5                 3   tes3
3  t                 4   tes4
4  g

Notice that in the first column of the 2nd excel, 2 is missing, so I want the first excel to look like this,

请注意,在第二个excel的第一列中,缺少2,所以我希望第一个excel看起来像这样,

1 tes1 t
2      5 
3 tes3 t
4 tes4 g

I am new to excel. Any help on this will be highly appreciated.

我是excel的新手。任何有关这方面的帮助将受到高度赞赏。

1 个解决方案

#1

Sub left_join()
Dim res As Variant
Dim i As Long, lastUsedRowSh1 As Long, lastUsedRowSh2 As Long
Dim cell As Range
Sheets(3).Cells.ClearContents
Sheets(1).Range("a:b").Copy Destination:=Sheets(3).Range("a1")
Sheets(3).Columns(2).Insert Shift:=xlToRight
lastUsedRowSh1 = Sheets(1).Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
lastUsedRowSh2 = Sheets(2).Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
i = 1
For Each cell In Sheets(1).Range("a1:a" & lastUsedRowSh1)
    On Error Resume Next
    res = Application.WorksheetFunction.VLookup(cell.Value, Sheets(2).Range("a1:b" & lastUsedRowSh2), 2, 0)
        If Err.Number = 0 Then
            Sheets(3).Range("b" & i).Value = res
            i = i + 1
        Else
            i = i + 1
        End If
Next cell
End Sub

You can even solve with a simple formula.

你甚至可以用一个简单的公式来解决。

Foglio1

A   B
1   t
2   5
3   t
4   g

Foglio2

A   B
1   tes1
3   tes3
4   tes4

Foglio3

Copy the content of Foglio1 in Foglio3, then run this formula

复制Foglio3中Foglio1的内容,然后运行此公式

=IF(ISERROR(VLOOKUP(Foglio1!A1,Foglio2!$A$1:$B$3,2,0))=TRUE,"",VLOOKUP(Foglio1!A1,Foglio2!$A$1:$B$3,2,0))

and drag it down. Regards.

并将其拖下来。问候。