Excel VBA que simula la funcionalidad SQL "no incluida"

Question

Todas -

Tengo un Excel de 2 hojas.

La hoja 1 tiene tres columnas (nombre, fecha, valor) La hoja 2 es el nombre.

Quiero escribir un script de VBA que muestre todos los datos de la Hoja 1 que NO tienen ninguno de los campos de nombre enumerados en la Hoja 2 en cualquier lugar de la hoja 1 (el nombre puede aparecer en diferentes columnas, por lo que lo ideal sería buscar todas las celdas en la Hoja 1) para aparecen en la hoja 3

Vea la imagen de muestra para una idea aproximada de lo que espero lograr. He buscado pero no he tenido suerte.

Answer 1

Si tiene Excel 365, puede usar las fórmulas de matriz dinámica

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

Ejemplo:

Datos (Hoja1)

Lista de exclusión (Hoja2)

Resultado

_{Nota: esto excluye los encabezados porque la etiqueta del encabezado Nameestá presente tanto en la columna de datos como en la columna de exclusión, así que asegúrese de mantener eso}

Answer 2

Sin Excel 365, recomendaría una 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

Introdúzcalo como una fórmula de matriz (complételo con Ctrl+ Shift+ Enter) en un rango lo suficientemente grande para contener los datos devueltos (puede ser mayor), y páselo su rango de datos de entrada y rango de exclusión (ambos como columnas completas)

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

Answer 3

¡Bienvenido a Stack Overflow!

No dijo dónde comienzan la tabla de origen y la tabla de criterios, ni dónde colocar el resultado del "anti-filtro". Escribí este código asumiendo que todos comienzan en la primera celda de la hoja de trabajo, 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

Libro de trabajo con macro, datos de prueba y ejemplos de criterios de selección en Sheet2

Si la macro no funciona como se esperaba, asegúrese de tener hojas denominadas Hoja1, Hoja2 y Hoja3 en su libro, y que el rango de datos de origen y el rango de criterios comiencen con las celdas A1. Si este no es el caso, realice los cambios necesarios en el texto de la macro: