Como posso filtrar dados com base em itens de uma lista?

Question

Como posso filtrar entradas contendo qualquer palavra específica em uma lista de palavras? Por exemplo, tenho uma lista de nomes de ruas em Cingapura.

Amoy Street, Cingapura
Monte de Ann Siang
Estrada de Anson
Rua árabe de
Rua arménia, Singapore
Rua de BBaghdad (Singapore)
de
Estrada de Balestier
Rua de Banda
Estrada de Bartley
Estrada da praia, Singapore
Rua Bencoolen
Bernam Street
Barco Quay
Boon Tat Street
Estrada do limite, Singapore
Bras Basah Road
Rua de Bugis
Estrada de Bukit Batok
Estrada de Bukit Pasoh
Estrada de Bukit Timah
Estrada de CCantonment, Singapore
Estrada de Choa Chu Kang
Clarke Quay
Estrada de Clementi
Club Street
Quay de Collyer
de
Não Perca! Connaught Drive: Avaliações de viajantes Estrada de Craig (Singapore)
de
Cruz Street
 e muito mais

Minha planilha tem um grande número de entradas, como as seguintes, que podem ou não conter os nomes das estradas mencionadas na minha lista:

1. Vi um acidente na Thomson Road
2. Encontrei isso por acidente
3. 6 veículos bateram na estrada de Balestier
4. Eu quero bater agora. Tão cansado.
5. Ônibus colide com bicicleta na Arab Street.
6. Acidente na City Road.
7. Você pode invadir minha casa mais tarde.

Como filtrar para retornar entradas que contenham qualquer nome de estrada identificado na lista de nomes? Como faço para introduzir uma matriz / lista de nomes de estradas no Microsoft Excel e relacioná-las a uma função de filtro?

    

Answer 1

Primeiro de tudo, se você quiser combinar frases que contenham "Amoy Street", você precisará remover o ", Singapura" da primeira entrada (e os outros que o contêm, e da mesma forma com "(Cingapura) ”). Você pode fazer isso com uma fórmula em uma coluna auxiliar, portanto, suponhamos que sua lista minimizada de nomes de estradas esteja nas células B1 a B42 . E suponha que suas sete sentenças de exemplo (ou dezessete mil, ou quantas você tiver) estão na Coluna C . Digite

=AND(ISERROR(SEARCH(B$1:B$42, C1)))

na célula D1 e digite Ctrl + Deslocar + Enter . Isso insere a fórmula como uma fórmula de matriz , fazendo com que ela apareça nas chaves: %código%. Arraste / preencha para baixo para cobrir a coluna {=AND(ISERROR(SEARCH(A$1:A$3, B1)))} . Isso será avaliado como FALSE para cada frase ( C value) que contiver uma string de C e TRUE para cada uma que não o fizer. Você pode filtrar isso. Se você não gosta do fato de esses valores estarem "atrasados", apenas envolva a expressão em B1:B42 :

=NOT(AND(ISERROR(SEARCH(B$1:B$42, C1))))

Isso é insensível a maiúsculas e minúsculas. Se você quiser fazer distinção entre maiúsculas e minúsculas, substitua NOT( ) por SEARCH .

Esta solução encontra substrings. Tornar as maiúsculas e minúsculas reduz a probabilidade de falsos positivos como "Banson Road" e "Uboat Quay". Se o nome da estrada nunca aparecer no início de uma frase, você poderá eliminar esses falsos positivos da correspondência de substring ao preceder um espaço a cada nome de estrada na coluna FIND . Se um nome de estrada puder aparecer no início de uma frase, então faça isso e mude a fórmula para

=AND(ISERROR(SEARCH(B$1:B$42," " & C1)))

Para lidar com "Mr. Connaught dirige um Mercedes ”, faça a mesma coisa, mas anexe os espaços. Isso ainda não vai lidar "O Sr. Connaught dirige um Mercedes?", então você pode querer seguir a rota que diferencia maiúsculas de minúsculas.

Eu testei esta solução para um pequeno número de strings. É possível que ele falhe se a lista de estradas for muito longa.

    

Answer 2

Use o AutoFiltro com a opção Contém ... . Isso permitirá que você visualize itens que contenham uma determinada string de texto dentro deles. Linhas que não contêm a string serão ocultadas.

Referência

EDIT # 1 :

Como James Jenkins apontou, o uso do AutoFiltro com Contém ... não permitirá que uma longa lista de subcordas incluídas seja aplicada simultaneamente ........... aqui é uma abordagem alternativa baseada em VBA.

Digamos que temos uma pasta de trabalho com duas guias "estradas" e "itens". A lista de estradas está na guia "estradas", começando em A2 . A lista de itens a serem filtrados está na guia "itens", começando em A2 .

Esta macro primeiro lê a lista de estradas em um array. A matriz é aplicada em cada item na lista de itens. Cada linha de item é ocultada ou exibida.

Sub FilterByList()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("roads")
    Set s2 = Sheets("items")
    Dim N1 As Long, N2 As Long, L1 As Long, L2 As Long
    Dim r1 As Range, r2 As Range

    N1 = s1.Cells(Rows.Count, "A").End(xlUp).Row
    N2 = s2.Cells(Rows.Count, "A").End(xlUp).Row
    ReDim rds(1 To N1 - 1) As String
    For L = 2 To N1
        rds(L - 1) = s1.Cells(L, 1)
    Next L

    s2.Cells.EntireRow.Hidden = False
    For L2 = 2 To N2
        Set r2 = s2.Cells(L2, "A")
        v2 = r2.Value
        r2.EntireRow.Hidden = True
        For L1 = 2 To N1
            If InStr(1, v2, rds(L1 - 1)) > 0 Then
                r2.EntireRow.Hidden = False
            End If
        Next L1
    Next L2
End Sub

    

Answer 3

Você pode usar o Filtro Avançado. Dê uma olhada no post abaixo:

link