excel - 为 3 个电子表格中不匹配的特定行 ID 创建表输出

Question

我想创建一个 VBA 脚本，它将输出一个电子表格，其中填充了所有 3 个表中的列，其中每个电子表格表中的指定行 ID 不匹配（全部 3 个）。所以如果 Name 和 Surname 是 ID

 Table 1
 Name  | Surname | Age | Date     | Bank Account No.
 John  | Marley  | 23  | 21/07/19 | 25511581125
 Simon | Harvey  | 22  | 04/03/19 | 25513321125

 Table 2
 Name  | Surname | Age | Date     | Gender
 John  | Marley  | 23  | 21/07/19 | Male
 Simon | Harvey  | 22  | 04/03/19 | Male

 Table 3
 Name  | Surname | Age | Date     | Height
 John  | Marley  | 23  | 21/07/19 | 5ft
 Simon | John    | 18  | 21/07/19 | 2ft

---

 Output

 Name  | Surname | Age | Date     | Bank Account No.
 Simon | Harvey  | 22  | 04/03/19 | 25513321125


 Name  | Surname | Age | Date     | Gender
 Simon | Harvey  | 22  | 04/03/19 | Male


 Name  | Surname | Age | Date     | Height
 Simon | John    | 18  | 21/07/19 | 2ft

---

所以我想要的是每个表中每一行的输出，其中在所有 3 个表中都没有找到该行中的指定 ID。我希望这是有道理的。指定的 ID 必须恰好出现在所有 3 个表中。有一个输出将保留一个列，其中所有 3 个表的确切名称都相同，这将是理想的，但不需要。

我将 ADODB 添加为标签的原因是我知道这将是一个更简单、更有效的解决方案？

我知道仅删除与所有 3 匹配的那些可能会更容易？或突出显示它们或其他东西。

如果有人对我应该如何完成这个有任何理论，我很想听听。谢谢！

标签： excelvbaadodb

Answer 1

尝试：

Option Explicit

Sub test()

    Dim rng1 As Range, rng2 As Range, rng3 As Range, cell1 As Range, cell2 As Range, cell3 As Range
    Dim ID As String
    Dim LastRowG As Long, Times As Long

    With ThisWorkbook.Worksheets("Sheet1")

            Set rng1 = .Range("F3:F5")
            Set rng2 = .Range("F9:F11")
            Set rng3 = .Range("F15:F17")

        Times = 0

        For Each cell1 In rng1

            ID = cell1.Value

            If Application.WorksheetFunction.CountIf(rng2, ID) + Application.WorksheetFunction.CountIf(rng3, ID) < 2 Then

                Times = Times + 1

                LastRowG = .Cells(.Rows.Count, "H").End(xlUp).Row

                If Times = 1 Then
                    .Range("H" & LastRowG + 1).Value = "Name"
                    .Range("I" & LastRowG + 1).Value = "Surname"
                    .Range("J" & LastRowG + 1).Value = "Age"
                    .Range("K" & LastRowG + 1).Value = "Date"
                    .Range("L" & LastRowG + 1).Value = "Bank Account No."
                    .Range("A" & cell1.Row & ":E" & cell1.Row).Copy .Range("H" & LastRowG + 2)
                Else
                    .Range("A" & cell1.Row & ":E" & cell1.Row).Copy .Range("H" & LastRowG + 1)
                End If

            End If

        Next cell1

        Times = 0

        For Each cell2 In rng2

            ID = cell2.Value

            If Application.WorksheetFunction.CountIf(rng1, ID) + Application.WorksheetFunction.CountIf(rng3, ID) < 2 Then

                Times = Times + 1

                LastRowG = .Cells(.Rows.Count, "H").End(xlUp).Row

                If Times = 1 Then
                    .Range("H" & LastRowG + 2).Value = "Name"
                    .Range("I" & LastRowG + 2).Value = "Surname"
                    .Range("J" & LastRowG + 2).Value = "Age"
                    .Range("K" & LastRowG + 2).Value = "Date"
                    .Range("L" & LastRowG + 2).Value = "Gender"
                    .Range("A" & cell2.Row & ":E" & cell2.Row).Copy .Range("H" & LastRowG + 3)
                Else
                    .Range("A" & cell2.Row & ":E" & cell2.Row).Copy .Range("H" & LastRowG + 1)
                End If

            End If

        Next cell2

        Times = 0

        For Each cell3 In rng3

            ID = cell3.Value

            If Application.WorksheetFunction.CountIf(rng2, ID) + Application.WorksheetFunction.CountIf(rng1, ID) < 2 Then

                Times = Times + 1

                LastRowG = .Cells(.Rows.Count, "H").End(xlUp).Row

                If Times = 1 Then
                    .Range("H" & LastRowG + 2).Value = "Name"
                    .Range("I" & LastRowG + 2).Value = "Surname"
                    .Range("J" & LastRowG + 2).Value = "Age"
                    .Range("K" & LastRowG + 2).Value = "Date"
                    .Range("L" & LastRowG + 2).Value = "Height"
                    .Range("A" & cell3.Row & ":E" & cell3.Row).Copy .Range("H" & LastRowG + 3)
                Else
                    .Range("A" & cell3.Row & ":E" & cell3.Row).Copy .Range("H" & LastRowG + 1)
                End If

            End If

        Next cell3

    End With

End Sub

结果：