![将 2 个具有共同值的 Excel 表合并到一列中](https://linux22.com/image/1369384/%E5%B0%86%202%20%E4%B8%AA%E5%85%B7%E6%9C%89%E5%85%B1%E5%90%8C%E5%80%BC%E7%9A%84%20Excel%20%E8%A1%A8%E5%90%88%E5%B9%B6%E5%88%B0%E4%B8%80%E5%88%97%E4%B8%AD.png)
我有 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
希望..希望..这有帮助!