[已解决]如何快速对比两工作表数据

czl103

详见附件，表1中有些合同编号是表2中没有的，表2中也有一些合同编号是表1中没有的，使用VLookup函数可以得到附件中的结果，问题是我们公司有几十万条的数据，使用函数就行不通了，一开始计算excel就不动了。

烦请大虾们帮忙设计一段VBA代码解决这个问题，不胜感激！

Sub 核对()
arr = Sheets("表2").Range("a2:a" & Sheets("表2").Range("a" & Rows.Count).End(xlUp).Row)
brr = Sheets("表1").Range("a2:a" & Sheets("表1").Range("a" & Rows.Count).End(xlUp).Row)
Set dic2 = CreateObject("scripting.dictionary")
Set dic1 = CreateObject("scripting.dictionary")
For i = 1 To UBound(arr)
  dic2(arr(i, 1)) = ""
Next
For i = 1 To UBound(brr)
  dic1(brr(i, 1)) = ""
Next

For i = 1 To UBound(arr)
  If dic1.exists(arr(i, 1)) Then arr(i, 1) = "表1存在" Else arr(i, 1) = "表1不存在"
Next
For i = 1 To UBound(brr)
  If dic2.exists(brr(i, 1)) Then brr(i, 1) = "表2存在" Else brr(i, 1) = "表2不存在"
Next

Sheets("表2").Range("C2:C" & Sheets("表2").Range("a" & Rows.Count).End(xlUp).Row) = arr
Sheets("表1").Range("C2:C" & Sheets("表1").Range("a" & Rows.Count).End(xlUp).Row) = brr

End Sub

最佳答案

月排行榜 / 总排行榜

wenzili

2017-4-12 20:57

吸取327718098的函数用法改进，速度稍快些
Public Sub master2()
Dim vaSht1data As Variant
Dim vaSht2data As Variant
Dim vaOutdata() As Variant
Dim i As Long, n As Long

vaSht1data = Intersect(Sheet1.Columns(1), Sheet1.UsedRange)
vaSht2data = Intersect(Sheet2.Columns(1), Sheet2.UsedRange)
ReDim vaOutdata(1 To UBound(vaSht1data, 1), 1 To 1)

    For i = 2 To UBound(vaSht1data, 1)
        If Application.WorksheetFunction.CountIf(Sheet2.Range("a:a"), vaSht1data(i, 1)) > 0 Then
            vaOutdata(i - 1, 1) = "YES"
        Else
            vaOutdata(i - 1, 1) = "NO"
        End If
    Next i
Sheet1.Range("c2").Resize(UBound(vaOutdata, 1), 1) = vaOutdata
ReDim vaOutdata(1 To UBound(vaSht2data, 1), 1 To 1)

   For i = 2 To UBound(vaSht2data, 1)
        If Application.WorksheetFunction.CountIf(Sheet1.Range("a:a"), vaSht2data(i, 1)) > 0 Then
            vaOutdata(i - 1, 1) = "YES"
        Else
            vaOutdata(i - 1, 1) = "NO"
        End If
    Next i
Sheet2.Range("c2").Resize(UBound(vaOutdata, 1), 1) = vaOutdata

End Sub

跳转到最佳答案楼层

327718098

Sub 查找()
Dim hh As Long, a As Long
hh = 2
Do While Sheet1.Cells(hh, 1) <> ""
a = Application.WorksheetFunction.CountIf(Sheet2.Range("A:A"), Sheet1.Cells(hh, 1))
If a = 0 Then
Sheet1.Cells(hh, 3) = "true"
End If
hh = hh + 1
Loop
hh = 2
Do While Sheet2.Cells(hh, 1) <> ""
a = Application.WorksheetFunction.CountIf(Sheet1.Range("A:A"), Sheet2.Cells(hh, 1))
If a = 0 Then
Sheet2.Cells(hh, 3) = "true"
End If
hh = hh + 1
Loop

End Sub

wenzili

Public Sub master()
Dim vaSht1data As Variant
Dim vaSht2data As Variant
Dim vaOutdata() As Variant
Dim i As Long, n As Long
vaSht1data = Intersect(Sheet1.Columns(1), Sheet1.UsedRange)
vaSht2data = Intersect(Sheet2.Columns(1), Sheet2.UsedRange)
ReDim vaOutdata(1 To UBound(vaSht1data, 1), 1 To 1)

    For i = 2 To UBound(vaSht1data, 1)
        For n = 2 To UBound(vaSht2data, 1)
            If vaSht1data(i, 1) = vaSht2data(n, 1) Then
                vaOutdata(i - 1, 1) = "YES"
            End If
        Next n
        If vaOutdata(i - 1, 1) <> "YES" Then vaOutdata(i - 1, 1) = "NO"
    Next i
Sheet1.Range("c2").Resize(UBound(vaOutdata, 1), 1) = vaOutdata
ReDim vaOutdata(1 To UBound(vaSht2data, 1), 1 To 1)

    For i = 2 To UBound(vaSht2data, 1)
        For n = 2 To UBound(vaSht1data, 1)
            If vaSht2data(i, 1) = vaSht1data(n, 1) Then
                vaOutdata(i - 1, 1) = "YES"
            End If
        Next n
        If vaOutdata(i - 1, 1) <> "YES" Then vaOutdata(i - 1, 1) = "NO"
    Next i
Sheet2.Range("c2").Resize(UBound(vaOutdata, 1), 1) = vaOutdata
   
End Sub

wenzili

327718098 发表于 2017-4-12 19:39
Sub 查找()
Dim hh As Long, a As Long
hh = 2

果然用已有函数速度快。
数据多的话，你的程序快的多

327718098

wenzili 发表于 2017-4-12 20:18
Public Sub master()
Dim vaSht1data As Variant
Dim vaSht2data As Variant

variant 的变量不用申明，可以直接用

wenzili

吸取327718098的函数用法改进，速度稍快些
Public Sub master2()
Dim vaSht1data As Variant
Dim vaSht2data As Variant
Dim vaOutdata() As Variant
Dim i As Long, n As Long

vaSht1data = Intersect(Sheet1.Columns(1), Sheet1.UsedRange)
vaSht2data = Intersect(Sheet2.Columns(1), Sheet2.UsedRange)
ReDim vaOutdata(1 To UBound(vaSht1data, 1), 1 To 1)

    For i = 2 To UBound(vaSht1data, 1)
        If Application.WorksheetFunction.CountIf(Sheet2.Range("a:a"), vaSht1data(i, 1)) > 0 Then
            vaOutdata(i - 1, 1) = "YES"
        Else
            vaOutdata(i - 1, 1) = "NO"
        End If
    Next i
Sheet1.Range("c2").Resize(UBound(vaOutdata, 1), 1) = vaOutdata
ReDim vaOutdata(1 To UBound(vaSht2data, 1), 1 To 1)

   For i = 2 To UBound(vaSht2data, 1)
        If Application.WorksheetFunction.CountIf(Sheet1.Range("a:a"), vaSht2data(i, 1)) > 0 Then
            vaOutdata(i - 1, 1) = "YES"
        Else
            vaOutdata(i - 1, 1) = "NO"
        End If
    Next i
Sheet2.Range("c2").Resize(UBound(vaOutdata, 1), 1) = vaOutdata

End Sub

wenzili

327718098 发表于 2017-4-12 20:55
variant 的变量不用申明，可以直接用

习惯了，不写总觉得缺些啥

327718098

wenzili 发表于 2017-4-12 20:58
习惯了，不写总觉得缺些啥

666

czl103

wenzili 发表于 2017-4-12 20:57
吸取327718098的函数用法改进，速度稍快些
Public Sub master2()
Dim vaSht1data As Variant

谢谢高手，我运行代码以后有如下错误提示，麻烦您帮忙看看什么原因

wenzili

czl103 发表于 2017-4-13 08:32
谢谢高手，我运行代码以后有如下错误提示，麻烦您帮忙看看什么原因

奥，这是运行时间的提示。可以将如下语句删掉
MsgBox Timer - t
我已在上面语句中删除了。