Correspondência de strings usando valores em uma planilha com lista longa (52K) de valores de uma coluna em outra planilha

Question

Na planilha 1: Eu tenho uma lista bastante curta (~ 75) de valores (strings de texto) e gostaria de encontrar todas as correspondências dessas strings com valores de uma coluna em outra planilha (planilha 2) ... esses valores pode existir em qualquer lugar dentro de uma string / passagem muito mais longa naquela coluna conhecida.

Além disso, eu gostaria de poder gerar uma terceira planilha com linhas iguais aos valores correspondentes ... construindo uma linha para cada uma, mas também contendo algumas das outras colunas da planilha 2.

Planilha 1

ABC
DEF
GHI
...
XYZ

Planilha 2

Column1 Column2 Column3
blah1   blahA   Chemical DEF is a compound
blah2   blahB   Compound 24231 (also known as GHI)...

Planilha 3

Col1  Col2    Col3
DEF   blah1   blahA
GHI   blah2   blahB

Isso pode ser feito com o Excel?

    

Answer 1

Primeiro, isso definitivamente levará algum tempo para ser feito em uma planilha do excel com 52k linhas, e como a coluna que contém a passagem na Planilha 2 está após as coisas desejadas na Planilha 3, acho que usar MATCH() é melhor.

Na planilha 3, copie e cole as cadeias de texto que você está procurando na planilha 2. Você removerá as que não forem encontradas com um filtro mais tarde (nesse caso, você removerá ABC e XYZ mais tarde).

Na coluna 2 da planilha 3, use a fórmula (isso pressupõe que você esteja usando cabeçalhos que estão na primeira linha de cada folha, caso contrário, altere A2 para A1 ):

=INDEX('Worksheet 2'!A:B,MATCH("*"&'Worksheet 3'!A2&"*",'Worksheet 2'!C:C,0),1)

E na coluna 3 da planilha 3, use a fórmula:

=INDEX('Worksheet 2'!A:B,MATCH("*"&'Worksheet 3'!A2&"*",'Worksheet 2'!C:C,0),2)

Arraste as duas fórmulas até o final da lista e aguarde até que os cálculos estejam concluídos. Depois disso, copie os dados e cole como valores (Colar Especial > Colar Valores. Isso acelerará as coisas) e aplique um filtro para que você possa remover todos os #N/A , se houver, da tabela. Ordene sua tabela para remover espaços entre as linhas.