用excel 匹配两列数据（查找相同或不同之处）

如果你有两列很长的数据，需要匹配他们是否相同，用excel可以解决这个问题。

匹配不同的：

把你的两列的数据放到A列和B列

按照截图的步骤点击到view 选项卡，点击Macros,然后新建一个把下面代码拷贝进去

[code]
Sub recipList()
Dim arr1, arr2, recipArr, x, i As Long
ReDim recipArr(i)
arr1 = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value
arr2 = Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Value
For Each x In arr1
If IsError(Application.Match(x, arr2, 0)) Then
ReDim Preserve recipArr(i)
recipArr(i) = x
i = i + 1
End If
Next
For Each x In arr2
If IsError(Application.Match(x, arr1, 0)) Then
ReDim Preserve recipArr(i)
recipArr(i) = x
i = i + 1
End If
Next
For k = LBound(recipArr) To UBound(recipArr)
Range("C" & k + 1) = recipArr(k)
Next
End Sub
[/code]

不需要保存，回到表格再点击macros,点击开始运行！

一会儿后就得到相应的数据了。两边对比，得出不一样的值，处理上万条数据应该是没问题的

还有一个匹配相同的：

根据上面步骤：代码如下

[code]
Sub Find_Matches()
Dim CompareRange As Variant, x As Variant, y As Variant
‘ Set CompareRange equal to the range to which you will
‘ compare the selection.
Set CompareRange = Range("C1:C5")
‘ NOTE: If the compare range is located on another workbook
‘ or worksheet, use the following syntax.
‘ Set CompareRange = Workbooks("Book2"). _
‘ Worksheets("Sheet2").Range("C1:C5")
‘
‘ Loop through each cell in the selection and compare it to
‘ each cell in CompareRange.
For Each x In Selection
For Each y In CompareRange
If x = y Then x.Offset(0, 1) = x
Next y
Next x
End Sub
[/code]

这里需要注意：把你要对比的数据放在A列和C列。结果会出现在B列。

Set CompareRange = Range(“C1:C5”) 这里的C1：C5就是比对从C1到C5.你可以修改 C1:C5000,就是C1-C5000

比对之前你需要选中A列的数值。

这个效率比较慢，处理几千条数据就会挂了。。。

这里还有方法可以比对。那就是把数据导入到sql，然后用sql语句对比。要懂sql的人才会哦。。

excel数据导入到sql教程可以点击这里查看

这里分享一个sql对比不同数据的sql语句：

SELECT yf.email FROM `key726` as yf WHERE yf.email not in (select `email` from `keydierzhouingame`)

匹配key726表里面的email不在 keydierzhouingame 表中的结果。

更多请留言！～～