VBA-比较2张纸上有差异的表 [英] VBA - Compare Tables on 2 Sheets with Differences

问题描述

我是VBA的新手，正在寻求帮助来编写可比较2个不同工作表上的两个表的同一列(B)，并将它们合并为第一张工作表中的单个表的子代码或代码.我已经研究了这样做的方法，并且对于使用范围或联合作为解决方案感到非常困惑.我希望它查找在工作表2的b列中都缺失的项目(它将在其中存储一个动态的但已知的名称)，并将整行添加到工作表1中(名为转储"，并在d列上附加注释)以及检查转储"中是否存在但另一张表中没有的行.只需要比较两张表中的b列，因为b列是关键.

I am new to VBA and was looking for help in writing a sub or code that can compare the same column (B) of two tables on 2 different sheets and combine them into a single table on the first sheet. I have looked at ways to do it and am really confused about using ranges or unions as a solution. I want it to find items both missing from column b in sheet 2 (which will have a dynamic, but known name stored in a variable) and add that entire row to sheet 1 (named 'Dump' with an additional comment on column d, as well as check for rows present in 'Dump' but not present in the other sheet. It only needs to compare column b of the two sheets as column b is the key.

这是给定2张数据以及最终输出的示例.

Here's an example of what I am looking for given 2 sheets of data, and the final output.

**Sheet 'Dump'**
+---------------------------+-----+------------------+---+
|             A             |  B  |        C         | D |
+---------------------------+-----+------------------+---+
| v62: Cheetah Mail         | v62 | 206              |   |
| c49: Report Suite         | c49 | appid            |   |
| v75: Message Type         | v75 | NDS Error        |   |
| v42: Core                 | v42 | fd8000d7         |   |
| c37: Message Key          | c37 | fd8000d7         |   |
+---------------------------+-----+------------------+---+

**Sheet 'ICD'**
+---------------------------+-----+-----------+---+
|             A             |  B  |     C     | D |
+---------------------------+-----+-----------+---+
| v62: Cheetah Mail         | v62 | 206       |   |
| c44: Portal               | c44 | polo      |   |
| v75: Message Type         | v75 | NDS Error |   |
| v42: Core                 | v42 | fd8000d7  |   |
| c37: Message Key          | c37 | fd8000d7  |   |
+---------------------------+-----+-----------+---+

Output Sheet 'Dump'
+--------------------+-----+-----------+---------------------------------------+
|         A          |  B  |     C     |                   D                   |
+--------------------+-----+-----------+---------------------------------------+
| v62: Cheetah Mail  | v62 | 206       |                                       |
| c44: Portal        | c44 | polo      | Item found in "ICD" but not in "Dump" |
| c49: Report Suite  | c49 | appid     | Item found in "Dump" but not in "ICD" |
| v75: Message Type  | v75 | NDS Error |                                       |
| v42: Core          | v42 | fd8000d7  |                                       |
| c37: Message Key   | c37 | fd8000d7  |                                       |
+--------------------+-----+-----------+---------------------------------------+

该行放置在哪里都没有关系，因为稍后将对其进行排序.非常感谢您的帮助

It doesn't matter where the row is placed as it will be sorted later. Thank you so much for the help

推荐答案

在这里，我为您准备了一个.我的代码可以为匹配两张纸给出正确的答案.但是顺序与您的顺序不相等.我认为结果行的顺序无关紧要.好，让我们检查一下我的代码:

Here, I got one for you. My code can give right answer for matching two sheet. But the order is not equal with yours. I think that it is no matter what is the order of result row. OK, let check my code:

Public Sub matchRow()

    Dim dumpSheet, icdSheet, outputSheet As Worksheet
    Dim startRow, outputRow, tempDumpRow, tempICDRow, icdRowCount, finishedICDIndex As Integer
    Dim finishedICD() As String
    Dim isExist As Boolean

    'Set sheets
    Set dumpSheet = Sheets("Dump")
    Set icdSheet = Sheets("ICD")
    Set outputSheet = Sheets("Output")

    'Set start row of each sheet for data
    startRow = 1
    outputRow = 1

    'Get row count from ICD sheet
    icdRowCount = icdSheet.Range("A:C").End(xlDown).row

    'Set index
    finishedICDIndex = 0

    'Re-define array
    ReDim finishedICD(0 To icdRowCount - 1)

    'Set the start row
    tempDumpRow = startRow

    'Here I looped with OR state, you can modify it to AND start if you want
    Do While dumpSheet.Range("A" & tempDumpRow) <> "" Or dumpSheet.Range("B" & tempDumpRow) <> "" Or dumpSheet.Range("C" & tempDumpRow) <> ""

        'Reset exist flag
        isExist = False

        'loop all row in ICD sheet
        For tempICDRow = 1 To icdRowCount Step 1

            'If row is not finished for checking.
            If UBound(Filter(finishedICD, tempICDRow)) < 0 Then

                'If all cell are equal
                If dumpSheet.Range("A" & tempDumpRow) = icdSheet.Range("A" & tempICDRow) And _
                   dumpSheet.Range("B" & tempDumpRow) = icdSheet.Range("B" & tempICDRow) And _
                   dumpSheet.Range("C" & tempDumpRow) = icdSheet.Range("C" & tempICDRow) Then

                    'Set true to exist flag
                    isExist = True

                    'Store finished row
                    finishedICD(finishedICDIndex) = tempICDRow

                    finishedICDIndex = finishedICDIndex + 1

                    'exit looping
                    Exit For

                End If

            End If

        Next tempICDRow

        'Show result
        outputSheet.Range("A" & outputRow) = dumpSheet.Range("A" & tempDumpRow)
        outputSheet.Range("B" & outputRow) = dumpSheet.Range("B" & tempDumpRow)
        outputSheet.Range("C" & outputRow) = dumpSheet.Range("C" & tempDumpRow)

        If isExist Then
            outputSheet.Range("D" & outputRow) = ""
        Else
            outputSheet.Range("D" & outputRow) = "Item found in ""Dump"" but not in ""ICD"""
        End If

        'increase output row
        outputRow = outputRow + 1

        'go next row
        tempDumpRow = tempDumpRow + 1

    Loop

    'loop all row in ICD sheet
    For tempICDRow = 1 To icdRowCount Step 1

        'If row is not finished for checking.
        If UBound(Filter(finishedICD, tempICDRow)) < 0 Then

            'Show result
            outputSheet.Range("A" & outputRow) = icdSheet.Range("A" & tempICDRow)
            outputSheet.Range("B" & outputRow) = icdSheet.Range("B" & tempICDRow)
            outputSheet.Range("C" & outputRow) = icdSheet.Range("C" & tempICDRow)
            outputSheet.Range("D" & outputRow) = "Item found in ""ICD"" but not in ""Dump"""

            'increase output row
            outputRow = outputRow + 1

        End If

    Next tempICDRow

End Sub

对于我的回答，我保证它将为任何数据集提供正确的答案.在这里，是我的代码的测试证据.

I guarantee for my answer that it will give the right answer for any data set. Here, my test evidence for my code.

转储表数据:

ICD工作表数据:

这是结果:

我知道这个答案与您的答案并不相同.但我相信这会对您有所帮助.

I know that this answer is not same in order with yours. But I believe that will be helpful for you.

这篇关于VBA-比较2张纸上有差异的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！