Excel VBA Simulation der SQL-Funktionalität „Nicht in“

Question

Alle -

Ich habe ein 2-Blatt-Excel.

Blatt 1 besteht aus drei Spalten (Name, Datum, Wert). Blatt 2 ist der Name.

Ich möchte ein VBA-Skript schreiben, das alle Daten in Blatt 1 anzeigt, in denen KEINES der in Blatt 2 aufgeführten Namensfelder irgendwo in Blatt 1 aufgeführt ist (der Name kann in verschiedenen Spalten angezeigt werden, sodass im Idealfall alle Zellen in Blatt 1 durchsucht werden) erscheinen in Blatt 3

Im Beispielbild finden Sie eine ungefähre Vorstellung davon, was ich erreichen möchte. Ich habe gesucht, aber kein Glück gehabt.

Answer 1

Wenn Sie über Excel 365 verfügen, können Sie die Dynamic Array-Formeln verwenden

=LET(Names,FILTER(Sheet1!$C:$E,Sheet1!$C:$C<>""),FILTER(Names,ISERROR(MATCH(INDEX(Names,,1),Sheet2!$G:$G,0))))

Beispiel:

Daten (Sheet1)

Ausschlussliste (Sheet2)

Ergebnis

_{Hinweis: Dies schließt die Header aus, da die Header-Bezeichnung Namesowohl in der Datenspalte als auch in der Ausschlussspalte vorhanden ist. Achten Sie also darauf, diese beizubehalten}

Answer 2

Ohne Excel 365. Ich würde eine UDF empfehlen

Function FilterList(ByVal Data As Range, ByVal Exclusion As Range) As Variant
    Dim Res As Variant
    Dim Dat As Variant
    Dim Excl As Variant
    Dim rw As Long
    Dim idx As Long
    Dim cl As Long
    Dim ExcludeIt As Variant
    Dim Cols As Long
    Dim TopRow As Long
    
    ReDim Res(1 To Application.Caller.Rows.Count, 1 To Application.Caller.Columns.Count)
    
    If IsEmpty(Data.Cells(1, 1)) Then
        TopRow = Data.Cells(1, 1).End(xlDown).Row
        Set Data = Data.Resize(Data.Rows.Count - TopRow).Offset(TopRow - 1)
    End If
    
    If IsEmpty(Data.Cells(Data.Rows.Count, 1)) Then
        Set Data = Data.Resize(Data.Cells(Data.Rows.Count, 1).End(xlUp).Row - Data.Row + 1)
    End If
    
    Dat = Data.Value
    Excl = Exclusion.Columns(1).Value
    
    Cols = Application.Min(UBound(Dat, 2), UBound(Res, 2))
    
    idx = 0
    For rw = 1 To UBound(Dat, 1)
        ExcludeIt = Application.Match(Dat(rw, 1), Excl, 0)
        If IsError(ExcludeIt) Then
            idx = idx + 1
            For cl = 1 To Cols
                Res(idx, cl) = Dat(rw, cl)
            Next
        End If
    Next
    For rw = 1 To UBound(Res, 1)
        For cl = IIf(rw <= idx, UBound(Dat, 2) + 1, 1) To UBound(Res, 2)
            Res(rw, cl) = vbNullString
        Next
    Next
    FilterList = Res
End Function

Geben Sie es als Array-Formel (vervollständigen Sie es mit Ctrl+ Shift+ Enter) in einem Bereich ein, der groß genug ist, um die zurückgegebenen Daten aufzunehmen (kann größer sein), und übergeben Sie ihm Ihren Eingabedatenbereich und Ausschlussbereich (beide als ganze Spalten).

=FilterList(Sheet1!$C:$E,Sheet2!$G:$G)

Answer 3

Willkommen bei Stack Overflow!

Sie haben nicht angegeben, wo die Quellentabelle und die Kriterientabelle beginnen oder wo das Ergebnis des "Anti-Filters" platziert werden soll. Ich habe diesen Code unter der Annahme geschrieben, dass alle in der ersten Zelle des Arbeitsblatts A1 beginnen:

Sub AntiFilter()
Dim aSource As Range, aCriteria As Range, oCell As Range, oTarget As Range, countCells As Long
    Set aSource = Worksheets("Sheet1").Range("A1").CurrentRegion
    countCells = aSource.Columns.Count
    Set aCriteria = Worksheets("Sheet2").Range("A1").CurrentRegion
    Set oTarget = Worksheets("Sheet3").Range("A1")
    aSource.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=aCriteria, Unique:=False
    For Each oCell In Application.Intersect(aSource, aSource.Columns(1))
        If oCell.RowHeight < 1 Then
            oCell.Resize(1, countCells).Copy Destination:=oTarget
            Set oTarget = oTarget.Offset(1, 0)
        End If
    Next oCell
    On Error Resume Next
    aSource.Worksheet.ShowAllData
    On Error GOTO 0
End Sub

Arbeitsmappe mit Makro, Testdaten und Beispielen für Auswahlkriterien auf Blatt 2

Wenn das Makro nicht wie erwartet funktioniert, stellen Sie sicher, dass Ihre Arbeitsmappe Blätter mit den Namen Sheet1, Sheet2 und Sheet3 enthält und dass der Quelldatenbereich und der Kriterienbereich mit den Zellen A1 beginnen. Ist dies nicht der Fall, nehmen Sie die erforderlichen Änderungen am Text des Makros vor: