数据核对

ZJ67223

有两张表，表格数量都超过了30万行。（附件表格已删减）两张表的数据有重合的部分，且第一张表的行数大于第二张表。我现在想把两张表重合的单据号找出来，代码应该如何写？请大神赐教！在线等！

ZJ67223

相同的单据号找出来且标上颜色

chaohuahch

代码如下，供测试：代码放在1号工作表内。

1. Sub fyExcelVBA()
   
2. Dim arr, brr, i%, dic As Object
   
3. Dim path1$, path2$
   
4. Application.DisplayAlerts = False
   
5. Application.ScreenUpdating = False
   
6. Set dic = CreateObject("scripting.dictionary")
   
7. Set dic1 = CreateObject("scripting.dictionary")
   
8. path1 = ThisWorkbook.path & ""
   
9. path2 = path1 & "出库明细-2.xlsx"
   
10. arr = Range("a1").CurrentRegion
    
11. For i = 2 To UBound(arr)
    
12.    dic(arr(i, 1)) = ""
    
13. Next i
    
14. With Workbooks.Open(path2)
    
15. brr = .Sheets(1).Range("a1").CurrentRegion
    
16. For i = 2 To UBound(brr)
    
17.    dic1(brr(i, 1)) = ""
    
18.    If dic.exists(brr(i, 1)) Then
    
19.       .Sheets(1).Cells(i, 1).Interior.ColorIndex = 8
    
20. '      .Sheets(1).Cells(i, 1) = ""
    
21.    End If
    
22. Next i
    
23. .Close 1
    
24. End With
    
25. For i = 2 To UBound(arr)
    
26.    If dic1.exists(arr(i, 1)) Then
    
27.      Cells(i, 1).Interior.ColorIndex = 8
    
28.    End If
    
29. Next i
    
30. Set dic = Nothing
    
31. Set dic1 = Nothing
    
32. Application.DisplayAlerts = True
    
33. Application.ScreenUpdating = True
    
34. 
    
35. End Sub
    

复制代码

jiminyanyan

http://club.excelhome.net/thread-1143188-1-1.html

LMY123

Option Explicit
Sub ADO_双薄相同数据()
    Dim cnn As Object, SQL As String
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.Path & "\出库明细-2.xlsx"
    SQL = "select a.* from [Sheet1$] a left join "
    SQL = SQL & "[Excel 8.0;DATABASE=" & ThisWorkbook.FullName & "].[Sheet1$] b on b.外部单号=a.发货单号 "
    Sheet3.Range("a2").CopyFromRecordset cnn.Execute(SQL)
    cnn.Close
    Set cnn = Nothing
End Sub