Aug032012
用excel 匹配两列数据(查找相同或不同之处)
如果你有两列很长的数据,需要匹配他们是否相同,用excel可以解决这个问题。
匹配不同的:
把你的两列的数据放到A列和B列
按照截图的步骤点击到view 选项卡,点击Macros,然后新建一个 把下面代码拷贝进去
本文来自山猫的博客地址:http://shanmao.me/?p=1066
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
不需要保存,回到表格再点击macros,点击开始运行!
一会儿后就得到相应的数据了。两边对比,得出不一样的值,处理上万条数据应该是没问题的
还有一个匹配相同的:
根据上面步骤:代码如下
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
这里需要注意: 把你要对比的数据放在A列和C列。结果会出现在B列。
Set CompareRange = Range(“C1:C5″) 这里的C1:C5就是比对从C1到C5.你可以修改 C1:C5000,就是C1-C5000
比对之前你需要选中A列的数值。
这个效率比较慢,处理几千条数据就会挂了。。。
这里还有方法可以比对。那就是把数据导入到sql,然后用sql语句对比。要懂sql的人才会哦。。
这里分享一个sql对比不同数据的sql语句:
SELECT yf.email FROM `key726` as yf WHERE yf.email not in (select `email` from `keydierzhouingame`)
匹配key726表里面的email不在 keydierzhouingame 表中的结果。
更多请留言!~~