如何用VBA代码查询两列数据差异?

照例打个响指,举个栗子。
如上图所示,查询A列和C列的数据,提取出相同值、A列存在C列不存在的值、A列不存在C列存在的值……
查询结果如下图所示。
当然,这事儿并不是一定得用VBA,函数和Power Query都可以的。但我们是VBA公众号嘛,所以还是讲VBA的方法……
代码如下:
代码运行动画:

代码看不全可以左右拖动..▼
Sub CheckDataDiff()    Dim d As Object    Dim aData1, aData2, aRes, aKeys    Dim strKey As String, strMsg As String    Dim i As Long, k As Long    Dim intSame As Long, intShtA As Long, intShtB As Long    Set d = CreateObject('scripting.dictionary') '后期绑定字典    With Worksheets('表1') '表1 A列数据存入数组        aData1 = .Range('a1:a' & .Cells(Rows.Count, 1).End(xlUp).Row)    End With    With Worksheets('表2') '表2 A列数据存入数组        aData2 = .Range('a1:a' & .Cells(Rows.Count, 1).End(xlUp).Row)    End With    For i = 2 To UBound(aData1) '遍历表1数据存入字典        strKey = aData1(i, 1)        d(strKey) = '表1' '将来源作为item    Next    ReDim aRes(1 To UBound(aData1) + UBound(aData2), 1 To 3) '定义结果数组大小    For i = 2 To UBound(aData2) '遍历表2数据        strKey = aData2(i, 1)        If d.exists(strKey) Then '如果存在关键字……            If d(strKey) = '表1' Then '如果该关键字属于表1,这层判断是为了避免表2存在重复值                intSame = intSame + 1 '累加相同个数                aRes(intSame, 1) = strKey '存入结果数组第1列                d(strKey) = '相同' '将关键字对应的item修改为相同            End If        Else '如果字典不存在该关键字,说明是表2独有            intShtB = intShtB + 1 '累加B表独有个数            aRes(intShtB, 3) = strKey '存入结果数组第3列            d(strKey) = '表2' '存入字典,item为来源表2        End If    Next    aKeys = d.keys '字典的keys集合    For i = 0 To UBound(aKeys) '遍历字典剔除tiem相同的即为A表独有值        strKey = aKeys(i)        If d(strKey) = '表1' Then            intShtA = intShtA + 1 '累加A表独有个数            aRes(intShtA, 2) = strKey '存入结果数组第2列        End If    Next    If k < intSame Then k = intSame    If k < intShtA Then k = intShtA    If k < intShtB Then k = intShtB    Worksheets('结果').Select    Range('a:e').ClearContents    Range('a1').Resize(UBound(aData1), 1) = aData1 'A列放表1数据    Range('b1').Resize(UBound(aData2), 1) = aData2 'B列放表2数据    Range('a1:e1') = Array('A表数据', 'B表数据', '相同项', 'A表独有', 'B表独有')    Range('c2').Resize(k, UBound(aRes, 2)) = aRes '结果数组数据    strMsg = '两表相同项:' & intSame & vbCrLf _            & 'A表独有项:' & intShtA & vbCrLf _            & 'B表独有项:' & intShtB    MsgBox strMsg, , '公众号Excel星球'    Set d = NothingEnd Sub

代码解析▼

第8行至第10行代码将表1 A列的数据存入数组aData1。

第11行至第13行代码将表2 A列的数据存入数组aData2。

第14行至第17行代码遍历aData1的数据,作为关键字存入字典,并将对应的item设置为来源表的名字'表1'。

第18行代码声明一个结果数组aRes。结果数组的行数原本是未知的,但最大行不会超过两个数据源行数的合计值;列数是已知的,有3列,第1列存放两表相同项,第2列存放A表独有项,第3列存放B表独有项。

第19至第32行代码遍历数组aData2。

第20行代码将aData2的数据赋值字符串变量strKey。

第21行代码判断字典中是否存在strKey。

如果存在,同时对应的item为表1,则说明该值属于两表相同项,存入结果数组第1列。

如果不存在,说明该值B表独有,则存入结果数组第3列,即B表独有项;同时将该值存入字典,item设置为'表2',避免表2存在重复值时,结果数组出现项目重复统计问题。

第33行至第40代码遍历字典的Key,如果Key对应的Item为'表1',则说明是表1独有项,存入结果数组第2列。

第41行至第43行代码通过比较运算,获取结果数组有效行的最大行数,赋值变量k。

第49行代码将结果数组的数据写入工作表单元格区域。

第50行和第51行代码弹窗告知用户相同项、两表独有项的数目。

……

下载案例模版,点击【运行】按钮,即可快速查看两列数据异同项,动手试一下吧▼

百度网盘▼

https://pan.baidu.com/s/1FyVdDFCAxQSQNc2pKkRRRw

(0)

相关推荐

  • 每日一题 剑指offer(二叉搜索树的后序遍历序列)

    编程是很多偏计算机.人工智能领域必须掌握的一项技能,此编程能力在学习和工作中起着重要的作用.因此小白决定开辟一个新的板块"每日一题",通过每天一道编程题目来强化和锻炼自己的编程能力 ...

  • 用VBA代码查询两列数据差异

    打个响指,举个栗子. 如上图所示,查询A列和C列的数据,提取出相同值.A列存在C列不存在的值.A列不存在C列存在的值-- 查询结果如下图所示. 代码如下: Sub CheckTwoClnData() ...

  • 一对多查询经典函数组合对比并提取两列数据差异

    点击上方 蓝色 文字  关注我们吧! 送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 对比两列数据差异,我们使用最多的方法是条件格式.今天向大家分享一个函数的方 ...

  • 学一个找两列数据差异的快捷键

    大家好,经常有朋友问两列数据找差异的问题,今天来统一回答一下.这个问题我之前利用Countif函数做过一个方法,大家可以先回忆一下. 点击查看:[两列数据找差异] 如果觉得函数法有点麻烦,可以看看今天 ...

  • 核对两列数据差异,数据太多怎么办?

    下图中有两组数据,一组是财务部提供的客户名单,一组是销售部提供的客户名单: 现在咱们根据两列数据来进行对比,需要完成三个问题: 1.提取出仅财务部有而销售部没有的客户名单. 2.提取出仅销售部有而财务 ...

  • Excel如何用箭头条件格式实现两列数据的比较

    单纯的数据对人眼球的吸引不足,但如果加上了图标那就不一样了. 操作 步骤1:对B.C两列数据做一个差值的辅助列,如图5-25所示. 图5-25 步骤2:用需要显示箭头图标的单元格减去另一列单元格,如图 ...

  • excel表格两列数据对比不同如何用颜色

    大哥,在excel中怎样比较两列数据的大小,并用不同... 选择A1单元格使用条件格式中的 使用公式确定,公式参考: =a1>b1 就可以判断A1是否大于B1,然后设定需要的填充颜色(或字体颜色 ...

  • EXCEL如何将两列数据合并为一列并在中间加符号

    EXCEL具有多列的功能,那么如何将两列,转化为一列呢,转化了之后,是否可以在两列之间,加上一些符号呢 工具/原料 EXCEL2007 方法/步骤 1 首先在电脑上打开一个新的工作簿,用来输入数据 2 ...

  • 如何实现在Excel比较两列数据大小并用颜色标记

    Excel表格是大家常用来处理数据的应用程序之一,在使用表格的时候,经常会遇到各种各样的问题.最近身边就有同事问我怎么样快速地实现表格中两列数据大小进行对比,并且对小的数据自动颜色标记.如果表格数据较 ...

  • 你以为把两列数据合在一起很简单?把这几种情况想明白再说!

    今天,视频群里的小伙伴们提了个问题:如何将B列和C列数据生成相应的公有(交集).独有(补集).所有(并集)? 这个问题咋一看挺简单的,从思路上讲,可以分为2种: 1.采用操作的方式,可以直接将两列数据 ...