☰

将 2 个具有共同值的 Excel 表合并到一列中

tag-icon • tag-icon microsoft-excel merge
将 2 个具有共同值的 Excel 表合并到一列中

我有 2 张 Excel 表。我想根据特定列中的公共值将它们合并起来。

例子：

如果 excel-A 有这样的值

orderNo  Product

C1-231  Spares
C1-232  Back-Ups
C1-242  SmatTerminal

和 Excel-B 类似

Type            Activity       orderNo      Date

Standard        Maintenance     C1-230     2012-12-01
Standard        Maintenance     C1-231     2012-12-01
Standard        Maintenance     C1-232     2012-12-01
Standard        Quality         C1-240     2012-12-01

然后我想要我的输出文件像

orderNo   Product           Type             Activity    orderNo       Date

C1-231    Spares            Standard        Maintenance  C1-231     2012-12-01
C1-232    Back-Ups          Standard        Maintenance  C1-232     2012-12-01
C1-242    SmatTerminal       
                            Standard        Maintenance  C1-230     2012-12-01
                            Standard        Quality      C1-240     2012-12-01

我不知道如何将它们结合起来。有人可以指导我做到这一点吗？

答案1

在 VBA 中...我将通过 2 个循环来完成

假设 Excel A 是工作表1，excel B 是工作表2您的结果表是工作表3.. 从 A2 开始

Sub MergeIt()
Dim LastA,LastA2 as Range
Dim y,y2 as Integer

Set LastA = Range("Sheet1!A65536").End(xlUp)
Set LastA2 = Range("Sheet2!A65536").End(xlUp)

For y = 2 to LastA.Row
  Sheet3!Cells(y,1) = Sheet1!Cells(y,1)
  Sheet3!Cells(y,2) = Sheet1!Cells(y,2)
  For y2=2 To LastA2
    If Sheet2!Cells(y2,3) = Sheet1!Cells(y,1) Then
      Sheet2!Cells(y2,5) = "v"

      Sheet3!Cells(y,3) = Sheet2!Cells(y2,1)
      Sheet3!Cells(y,4) = Sheet2!Cells(y2,2)
      Sheet3!Cells(y,5) = Sheet2!Cells(y2,3)
      Sheet3!Cells(y,6) = Sheet2!Cells(y2,4)
    End If
  Next
Next

y= y + 1
For y2 = 2 to LastA2
  If Sheet2!Cells(y,5) <> "v" Then
    Sheet3!Cells(y,3) = Sheet2!Cells(y2,1)
    Sheet3!Cells(y,4) = Sheet2!Cells(y2,2)
    Sheet3!Cells(y,5) = Sheet2!Cells(y2,3)
    Sheet3!Cells(y,6) = Sheet2!Cells(y2,4)
  End If
Next

End Sub

希望..希望..这有帮助！

相关内容