Como faço para combinar uma string parcial na célula com uma lista do Excel 2013?

Question

Eu tenho as seguintes informações em uma célula

| John Smith 34, Manager  | 
| Jane Doe 25, Assistant  |
| Lucio 32, Web Developer |

Eu também tenho outra lista com uma lista de nomes

| Lucio            |
| John Smith       |
| Jane Doe         |
| Samuel L Jackson |

[editado para esclarecer a questão]

O que eu quero fazer é analisar os dados no primeiro conjunto de células, comparando os nomes com as informações do segundo conjunto de células, e colocar um 1, se eles corresponderem.

Como eu faria isso no excel 2013?

EDIT: Eu acho que muitas das respostas, devido ao meu erro, incompreenderam que as listas precisam combinar célula por célula. Eu só preciso ter certeza de que os nomes na primeira lista estão corretos e que eles correspondam a pelo menos um na segunda lista.

    

Answer 1

A partir do que você descreve, a maneira mais fácil de fazer o que você está pedindo seria escrever uma macro VBA como a fórmula. Mas sua pergunta é muito vaga. Por exemplo, você precisa dizer se a primeira lista contém nomes que não estão na segunda lista. Se a segunda lista contiver nomes que não estão na primeira lista. Também no seu item de exemplo 1 da primeira lista corresponde ao item 1 da segunda lista e assim por diante para os itens 2 e 3. Você está apenas comparando com a linha correspondente na segunda lista ou contra toda a lista? Eu poderia fazer suposições, mas espero que você entenda o que estou dizendo quando digo que sua pergunta é muito vaga.

    

Answer 2

Isso pode funcionar para você.

Se sua primeira lista estiver em A1: A3 e sua segunda lista estiver em D1: D3, insira isso na célula E1:

=IFERROR(IF(MATCH(D1,LEFT($A$1:$A$3,LEN(D1)),0)>0,1,0),0)

Use CTRL + Shift + Enter para torná-lo uma fórmula de matriz. Em seguida, copie e cole.

Eu verifiquei com um valor inválido em uma célula extra D4=Donald e ele retornará 0 se não houver correspondência exata.

EDITAR:

Eu provavelmente deveria explicar que a forma como essa fórmula funciona é que ela pegará a lista original A1:A3 , e reduzirá para a quantidade de caracteres que está na célula que queremos ver em D1 .

O LEFT($A$1:$A$3,LEN(D1)) voltará com uma matriz de 3 valores:

{"John Smith";"Jane Doe 2";"Lucio 32, "} .

Nessa lista, estamos procurando John Smith e uma correspondência exata (sem distinção entre maiúsculas e minúsculas) e, como há apenas uma, ela retorna 1 .

Em D2 , por exemplo, a matriz resultante de novos valores a serem verificados é:

{"John Smi";"Jane Doe";"Lucio 32"}

Como estamos procurando por Jane Doe , há uma correspondência que se ajusta exatamente e, portanto, 1 é retornado.

    

Answer 3

Se NameList refere-se ao intervalo (por exemplo, $ J $ 8: $ J $ 10) que contém sua lista de nomes:

Esta fórmula deve ser introduzida por matriz :

=COUNT(FIND(NameList,A1))

Para matriz-insira uma fórmula, depois de inserir a fórmula na célula ou barra de fórmulas, mantenha pressionada  enquanto batendo. Se você fez isso corretamente, o Excel colocará colchetes {...} ao redor da fórmula.

    

Answer 4

Corrija-me se estiver errado, mas o modo como entendo sua pergunta é que o texto do assunto e as strings de pesquisa já existem em uma ordem específica e você simplesmente gostaria de comparar as duas strings. ou seja,

| John Smith 34, Manager  | John Smith |
| Jane Doe 25, Assistant  | Jane Doe   |
| Lucio 32, Web Developer | Lucio      |

Eu uso a abordagem substitute() para evitar o uso excessivo de iferror() . Você pode comparar o comprimento do texto do assunto ("palheiro") com o comprimento do palheiro, substituindo a seqüência de pesquisa ("agulha").

= LEN(haystack) - LEN( SUBSTITUTE(haystack, needle, "") ) > 0

  // Analysis of a matching example

  - LEN("John Smith 34, Manager") = 22
  - SUBSTITUTE("John Smith 34, Manager", "John Smith", "") = " 34, Manager"
  - LEN(" 34, Manager") = 10
  - (22 - 10) > 0 = TRUE

  // Analysis of a non-matching example

  - LEN("John Smith 34, Manager") = 22
  - SUBSTITUTE("John Smith 34, Manager", "Lucio", "") = "John Smith 34, Manager"
  - LEN("John Smith 34, Manager") = 22
  - (22 - 22) > 0 = FALSE

No exemplo acima, você simplesmente usaria =LEN(A1)-LEN(SUBSTITUTE(A1,B1,""))>0 . Observe também que você pode realizar comparações sem distinção entre maiúsculas e minúsculas por meio de SUBSTITUTE(UPPER(haystack), UPPER(needle), "")

Eu prefiro essa abordagem em relação a iferror(find(needle, haystack)>0,false) , porque o tratamento de erros é inerente - mais de uma filosofia de que o tratamento de erros deve ser feito no nível macro em vez de em fórmulas.

Além disso, se você preferir a saída binária ( 0, 1 ) vs a saída booleana ( TRUE, FALSE ), você pode agrupar sua função em INT() (ou seja, INT(TRUE) = 1, INT(FALSE) = 0).