比较具有唯一列匹配的两个工作表-Java 学习之路

-1

如何以简单的方式比较大型数据集的Excel中的两张表格，无论是VBA还是公式？

我有两张，每张10列 . 每张纸都有B列，具有唯一的批号 . 我想比较两个工作表之间具有相同B列值的行，如果在其他9个列中有任何差异，则将整行复制到另一个新工作表 .

例如：
enter image description here

第一张是主表 . 表格1和表2具有相同的名称但名称"b"的年龄不同，因此我需要在表格3中使用名称b和年龄，并且年龄单元格在表格3中应该是彩色的，因为年龄不同 .

如果我有大量的列而不是仅仅很难识别的年龄，通常我会在每个列上使用 vlookup 作为唯一的名称并使用 if 函数来检查匹配与否 .

1 回答

您可以尝试下面给出的代码 .

Prerequisites: 1）代码假定工作簿中有三个名为Sheet1，Sheet2和Sheet3的工作表

2）Sheet1和Sheet2有10列，每列有相同的 Headers .

下面的代码将Sheet2上的数据与Sheet1上基于B列的数据进行比较，即如果在Sheet1的B列中找到Sheet2上的B列的值，则代码将比较两个工作表的所有列的数据，如果数据不匹配或不同，数据将被复制到Sheet3 .

Sub CompareAndCopyUnMatchedData()
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim x, y, z, dict1
Dim i As Long, j As Long
Dim str As String
Application.ScreenUpdating = False
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set ws3 = Sheets("Sheet3")
ws3.Cells.Clear
ws1.Range("A1:J1").Copy ws3.Range("A1")
x = ws1.Range("A1").CurrentRegion.Value
y = ws2.Range("a1").CurrentRegion.Value
Set dict1 = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(x, 1)
    dict1.Item(x(i, 2)) = x(i, 1) & "," & x(i, 2) & "," & x(i, 3) & "," & x(i, 4) & "," & x(i, 5) & "," & x(i, 6) & "," & x(i, 7) & "," & x(i, 8) & "," & x(i, 9) & "," & x(i, 10)
Next i
For i = 2 To UBound(y, 1)
    If dict1.exists(y(i, 2)) Then
        If dict1.Item(y(i, 2)) <> y(i, 1) & "," & y(i, 2) & "," & y(i, 3) & "," & y(i, 4) & "," & y(i, 5) & "," & y(i, 6) & "," & y(i, 7) & "," & y(i, 8) & "," & y(i, 9) & "," & y(i, 10) Then
            str = y(i, 1) & "," & y(i, 2) & "," & y(i, 3) & "," & y(i, 4) & "," & y(i, 5) & "," & y(i, 6) & "," & y(i, 7) & "," & y(i, 8) & "," & y(i, 9) & "," & y(i, 10)
            z = Split(str, ",")
            ws3.Range("A" & Rows.Count).End(3)(2).Resize(1, 10).Value = z
        End If
    End If
Next i
Set dict1 = Nothing
Application.ScreenUpdating = True
End Sub

Edit: 如果您有兴趣在Sheet2上突出显示与Sheet1不匹配的单元格！E10则Sheet2！E10将根据您在条件格式中设置的格式突出显示或格式化 .

用于条件格式的公式如下......

=A1<>Sheet1!A1

回复于 2024-04-25T18:13:35+08:00

比较具有唯一列匹配的两个工作表

1 回答

相关问题