时间戳

首页 > 解决方案 > 根据列 a 列 d 的值比较来自两个工作表的数据

excel - 根据列 a 列 d 的值比较来自两个工作表的数据

问题描述

我正在寻找一个 vba 代码，该代码循环遍历 sheet1 中的所有行，根据在 sheet2 中的 a 列和 d 列中找到匹配的 sheet1 中的 colmn a 和列 d 值查找数据匹配。示例列 a 1011 列 d john。如果在 sheet2 列 a = 1011 和列 d 同一行 = john 中某处，则找到匹配项。我需要将所有不匹配的内容保存到新工作簿中。

标签： excelvba

解决方案

尝试这个。它将结果放在此工作簿的 Sheet3 中。>>

Option Explicit  '  it is your friend

Sub do_FindOrphans()

'   create or find Sheet3, and clear it out
    If Not Evaluate("ISREF('Sheet3'!A1)") Then
        Sheets.Add After:=ActiveSheet
        ActiveSheet.Name = "Sheet3"
    Else
        Sheets("Sheet3").Activate
    End If
    Cells.Select
    Selection.ClearContents

'   initialize max row#, and copy in column headings
    Sheets("Sheet1").Select
    Dim s1row As Long, s1max As Long, s1col As Long, s1endcol As Integer
    s1max = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    Sheets("Sheet2").Select
    Dim s2row As Long, s2max As Long
    s2max = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
    Dim s3row As Long, matched As String

    With ActiveSheet
        s1endcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
    s3row = 1
    For s1col = 1 To s1endcol
        Sheets("Sheet3").Cells(s3row, s1col) = Sheets("Sheet1").Cells(1, s1col)
    Next s1col

'   step thru sheet1
    For s1row = 2 To s1max
        matched = "N"
'       step thru sheet2
        For s2row = 2 To s2max
'           Compare the keys
            If Sheets("Sheet1").Cells(1, s1row) = Sheets("Sheet2").Cells(1, s2row) _
            And Sheets("Sheet1").Cells(4, s1row) = Sheets("Sheet2").Cells(4, s2row) Then
                matched = "Y"
                Exit For
            End If
        Next s2row
        If matched = "N" Then
            s3row = s3row + 1
            For s1col = 1 To s1endcol
                Sheets("Sheet3").Cells(s3row, s1col) = Sheets("Sheet1").Cells(s1row, s1col)
            Next s1col
        End If
    Next s1row

End Sub

推荐阅读