Excel VBA Simulando funcionalidade SQL “Not In”

Question

Todos -

Eu tenho um excel de 2 folhas.

A folha 1 tem três colunas (nome, data, valor). A folha 2 é o nome.

Quero escrever um script VBA que exiba todos os dados da Folha 1 que NÃO têm nenhum dos campos de nome listados na Folha 2 em qualquer lugar da folha 1 (o nome pode aparecer em colunas diferentes, então o ideal seria pesquisar todas as células na Folha 1) para aparecem na folha 3

Veja a imagem de exemplo para uma ideia aproximada do que espero realizar. Pesquisei, mas não tive sorte.

Answer 1

Se você tem o Excel 365, você pode usar as fórmulas Dynamic Array

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

Exemplo:

Dados (Folha1)

Lista de Exclusão (Folha2)

Resultado

_{Observação: isso exclui os cabeçalhos porque o rótulo do cabeçalho Nameestá presente tanto na coluna Dados quanto na coluna Exclusão, portanto, certifique-se de manter isso}

Answer 2

Sem o Excel 365. Eu recomendaria um UDF

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

Insira-o como uma Fórmula de matriz (complete-o com Ctrl+ Shift+ Enter) em um intervalo grande o suficiente para conter os dados retornados (pode ser maior) e passe seu intervalo de dados de entrada e intervalo de exclusão (ambos como colunas inteiras)

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

Answer 3

Bem-vindo ao Stack Overflow!

Você não disse onde a tabela de origem e a tabela de critérios começam, ou onde colocar o resultado do "anti-filtro". Escrevi este código presumindo que todos eles começam na primeira célula da planilha, A1:

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

Pasta de trabalho com macro, dados de teste e exemplos de critérios de seleção na Planilha 2

Se a macro não funcionar conforme o esperado, certifique-se de que você tenha planilhas denominadas Plan1, Plan2 e Plan3 em sua pasta de trabalho e que o intervalo de dados de origem e o intervalo de critérios iniciem com células A1. Se não for o caso, faça as alterações necessárias no texto da macro: