Excel 2013: VLookup para células que compartilham caracteres comuns na célula, mas estão rodeadas por outro texto não correspondente

Question

Eu estou puxando informações de dois bancos de dados diferentes. Os bancos de dados usam um protocolo de nomenclatura diferente para o mesmo item / colocação especificada, no entanto, eles sempre têm certos componentes do nome em comum. A duração desses nomes pode variar ao longo de cada um dos bancos de dados (veja a foto abaixo), então não acho que contar personagens ajudaria. Eu preciso de uma fórmula (provavelmente um vlookup / match / index de algum tipo) para emparelhar os nomes do segundo nome do banco de dados com o primeiro nome do banco de dados e, em seguida, colocá-lo na coluna adjacente (B2) na planilha1. Até este ponto eu tive que combinar, copiar e colar os pares manualmente de uma folha para a outra e leva para sempre. Qualquer ajuda seria muito apreciada !!!

Por exemplo:

Nome do banco de dados1 na planilha1, A2: 728x90_Allstate_629930_ALL_JUL_2013_MASSACHUSETTSAUTO_BAN_MSN_ROSMSNAUTOSMASSACHUSETTS_7.2.13

Nome do banco de dados2 na planilha2, A13: BAN_MSN_ROSMSNAUTOSMASSACHUSETTS728X90_728X90_DFA

Fatores comuns: "ROSMSNAUTOSMASSACHUSETTS" & "728X90" Portanto, A2 e A13 precisam emparelhar

Em alguns casos, o Banco de Dados 1 e 2 terá um aspecto de nome comum, mas o dimensionamento será diferente. Eles precisam ter AMBOS aspectos em comum para serem emparelhados, então eu NÃO quero que o exemplo abaixo seja emparelhado.

Nome do banco de dados1 na planilha1, A2: 728x90_Allstate_629930_ALL_JUL_2013_MASSACHUSETTSAUTO_BAN_MSN_ROSMSNAUTOSMASSACHUSETTS_7.2.13

Nome do banco de dados2 na planilha2, A12: BAN_MSN_ROSMSNAUTOSMASSACHUSETTS300X250_300X250_DFA

Fator Comum: Apenas "ROSMSNAUTOSMASSACHUSETTS" corresponde. "728x90" não é igual a "300X250" - O tamanho é diferente, por isso não devem ser emparelhados.

    

Answer 1

Se houver pelo menos um aspecto consistente de cada convenção de nomenclatura, você poderá usar curingas e fórmulas para encontrar uma correspondência.

Por exemplo, se você souber que a convenção de nomenclatura do DB1 sempre segue o formato size_company_date _... , é possível usar algo assim para encontrar o tamanho no primeiro ID do banco de dados:

=MID(A1,1,FIND("_",A1,1)-1)

onde MID retorna o tamanho olhando para uma célula ( A1 ) e começando com o primeiro caractere retorna um certo número de caracteres (que é quando você usa Find para encontrar o número da posição do primeiro sublinhado da string menos 1).

Se você soubesse, então, onde o segundo valor correspondente sempre estava na convenção de nomenclatura, você poderia usar uma fórmula para encontrar a segunda parte da sua correspondência. Este artigo dá uma dica sobre isso.

Usando esse conhecimento, se você soubesse que seu segundo valor correspondente era sempre nove sublinhados à esquerda, você poderia usar essa fórmula para retornar a posição inicial de seu segundo valor correspondente:

=FIND(CHAR(1),SUBSTITUTE(G1,"_",CHAR(1),9))+1

Agora que sabemos como encontrar o tamanho e outro valor correspondente, podemos usar um Vlookup e curingas para corresponder ao resto. (Peço desculpas pela fórmula longa ...)

=VLOOKUP("*" & MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"_",CHAR(1),9)),FIND(CHAR(1),SUBSTITUTE(A1,"_",CHAR(1),10))-FIND(CHAR(1),SUBSTITUTE(A1,"_",CHAR(1),9))) & "*" & MID(A1,1,FIND("_",A1,1)-1) & "*",B:B,1,FALSE)

Esta fórmula faz as seguintes suposições:
 1. A convenção de nomenclatura do Database1 sempre começa com o tamanho.
2. A convenção de nomenclatura do Database1 sempre possui nove sublinhados antes do segundo valor correspondente.
3. Os valores do banco de dados1 estão na coluna A (a fórmula acima referencia o primeiro valor DB1 em A1 ).
4. Os valores do DB2 estão na coluna B .

Eu usei essa fórmula e seus exemplos em Excel 2010 e funcionou para mim. Boa sorte!