比较并找到2张相应列中的重复数据

2 回答

• 2

  如果您有"a lot"数据，那么在此处执行操作会产生严重的性能问题 . 问题是，每次将数据从Excel移动到VBA都是一种开销 . 你应该在这里做的是将所有数据一次复制到数组（参见http://www.cpearson.com/excel/ArraysAndRanges.aspx）并在VBA中完成所有逻辑而不触及Excel工作表 .

  如果你仍然需要提升性能，你应该查看字典（参见Does VBA have Dictionary Structure?） .

  阅读这篇文章：https://msdn.microsoft.com/en-us/library/office/ff726673.aspx特别是段"Read and Write Large Blocks of Data in a Single Operation"

  回复于 2024-04-26T08:47:33+08:00
• 0

  考虑一个SQL解决方案（假设您使用Excel for PC），因为Excel可以使用Jet / ACE SQL引擎（Windows .dll文件）在工作簿上运行ODBC连接 . 此处不使用循环或if / then逻辑跨单元格来实现可扩展，高效的解决方案 . 基本上你会运行两个查询：

  • MATCHES：结果和MasterList工作表上的内部联接查询，其结果附加到Follow-Ups

  SELECT r.* FROM [Results$] r
      INNER JOIN [MasterList$] m
      ON r.ColA = m.ColA AND r.ColB = m.ColB
  

  • NON-MATCHES：结果和MasterList工作表上的左连接空查询，其结果附加到MasterList

  SELECT r.* FROM [Results$] r
      LEFT JOIN [MasterList$] m
      ON r.ColA = m.ColA AND r.ColB = m.ColB
      WHERE m.ColA IS NULL;
  

  VBA 脚本（驱动程序/提供程序版本包含两个连接）

  Sub RunSQL()
  On Error GoTo ErrHandle
      Dim conn As Object, rst As Object
      Dim strConnection As String, strSQL As String
      Dim i As Integer
      Dim fLastRow As Integer, mLastRow As Integer
  
      Set conn = CreateObject("ADODB.Connection")
      Set rst = CreateObject("ADODB.Recordset")
  
      ' Hard code database location and name
  '    strConnection = "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" _
  '                      & "DBQ=C:\Path\To\Workbook.xlsm;"
      strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                         & "Data Source='C:\Path\To\Workbook.xlsm';" _
                         & "Extended Properties=""Excel 8.0;HDR=YES;"";"
  
      ' OPEN DB CONNECTION
      conn.Open strConnection
  
      ''''''''''''''''''''''''''''''''''''
      ''' FOLLOW-UPS (MATCHED) DATA
      ''''''''''''''''''''''''''''''''''''
      strSQL = " SELECT r.* FROM [RESULTS$] r" _
                & " INNER JOIN [MASTERLIST$] m" _
                & " ON r.ColA = m.ColA AND r.ColB = m.ColB"
  
      ' OPEN QUERY RECORDSET
      rst.Open strSQL, conn
  
      ' COPY DATA TO WORKSHEET
      fLastRow = Worksheets("FOLLOW-UPS").Cells(Worksheets("FOLLOW-UPS") _
                            .Rows.Count, "A").End(xlUp).Row
      Worksheets("FOLLOW-UPS").Range("A" & fLastRow + 1).CopyFromRecordset rst
      rst.Close
  
      ''''''''''''''''''''''''''''''''''''
      ''' MASTERLIST (UNMATCHED) DATA
      ''''''''''''''''''''''''''''''''''''
      strSQL = " SELECT r.* FROM [RESULTS$] r" _
                & " LEFT JOIN [MASTERLIST$] m" _
                & " ON r.ColA = m.ColA AND r.ColB = m.ColB" _
                & " WHERE m.ColA IS NULL;"
  
      ' OPEN QUERY RECORDSET
      rst.Open strSQL, conn
  
      ' COPY DATA TO WORKSHEET
      mLastRow = Worksheets("MASTERLIST").Cells(Worksheets("MASTERLIST") _
                            .Rows.Count, "A").End(xlUp).Row
      Worksheets("MASTERLIST").Range("A" & mLastRow + 1).CopyFromRecordset rst
  
      rst.Close
      conn.Close
  
      MsgBox "Successfully processed SQL queries!", vbInformation
      Exit Sub
  
  ErrHandle:
      MsgBox Err.Number & " = " & Err.Description, vbCritical
      Exit Sub
  End Sub
  

  Demo

  这是一个使用Shakespearan字符的Dropbox xlsm file演示，其中MasterList包含流行的女性角色，结果是一小批女性/男性角色 . 按SQL按钮运行宏 . 处理完查询后，女性（匹配）输出到Follow-Ups，男性（不匹配）附加到MasterList . 请务必在字符串ODBC连接中调整工作簿路径 .

  回复于 2024-04-26T08:47:33+08:00