Tenho 600 códigos de item e cada um deles é codificado com atributos no código geral do item. Por exemplo, podemos ter o item simples 600, depois 600BK (corpo preto) e 600BKR (corpo preto, detalhe em vermelho) e 600BKR-YEL (corpo preto, detalhe em vermelho, base amarela).
Então eu tenho a lista de códigos de itens:
600
600BK
600BKR
600BKR-YEL
e, em seguida, em uma planilha separada, uma lista de pesquisa de cada código e o que ele representa:
BK Black Body
R Red Detail
YEL Yellow Base
Gostaria de escrever uma função que encontrasse todos os códigos no título do item que está na planilha de pesquisa e, em seguida, retornasse todos os valores correspondentes para cada código encontrado, de preferência em uma célula.
600
600BK Black Body
600BKR Black Body Red Detail
600BKR-YEL Black Body Red Detail Yellow Base
Isso é possível?
Responder1
Eu faria algo como o seguinte. Supondo que sua lista de códigos de item esteja na coluna A da Planilha1 e, digamos, nas linhas 2 a 20, e sua lista de pesquisa esteja nas colunas A e B da Planilha2, linhas 2 a 10:
=IFERROR(VLOOKUP(MID(A2,4,2),Sheet2!$A$2:$B$4,2,0),"")&" "
&IFERROR(VLOOKUP(MID(A2,6,1),Sheet2!$A$2:$B$4,2,0),"")&" "
&IFERROR(VLOOKUP(MID(A2,8,3),Sheet2!$A$2:$B$4,2,0),"")
na célula B2 e depois copiado para todas as linhas da Planilha1.
Embora coloque as pesquisas em uma única fórmula, essa abordagem seria muito complicada se você tivesse muitos códigos. Eu recomendaria que você inserisse linhas auxiliares na Planilha1 para cada posição de código (por exemplo, mid(A2,4,2)) e depois as concatenas em uma única string.
Responder2
A resposta curta é “Sim”, uma forma é um pouco mais longa.
Como há muitas possibilidades não excluídas na sua pergunta (como itens simples codificados com mais de três números ou alfanuméricos, mais de três códigos por item simples, códigos que variam em significado dependendo do item simples, etc.), sugiro que seja mais seguro começar com a análise os códigos. Isso deve evitar complicações com GR sendo Corpo Cinza com Detalhes Vermelhos (geralmente um esquema de cores compatível!) ou Corpo Verde.
Supondo que eles tenham sido analisados (em três colunas B:D começando na Linha2), depois disso é fácil, com sua tabela de pesquisa (aquela na planilha separada) diga nomeado codes:
=VLOOKUP(B2,codes,2,0)&VLOOKUP(C2,codes,2,0)&VLOOKUP(D2,codes,2,0)
na sua folha de códigos de item. Para obter espaços entre os valores, presumi que todas as entradas na coluna direita de sua matriz de código terminam em um espaço (fácil de organizar, como =A2&" " copiado, etc).
Portanto, a parte difícil pode ser analisar os códigos dos códigos dos itens, para os quais sugiro adicionar colunas conforme abaixo (mais se houver mais de três códigos):
com fórmulas como abaixo:
A colunaB serve para definir onde começar a procurar os códigos (caso os códigos simples sejam diferentes de três numéricos). As colunas C:D são para onde começar a pesquisar o próximo código/o comprimento do próximo código. Eu concordaria que não é elegante, mas relativamente versátil. Certifique-se de que a análise esteja correta à esquerda antes de trabalhar para a direita.
Após a análise bem-sucedida, sugiro copiar/colar valores/valores especiais (para se livrar das fórmulas) e substituirem brancocom ponto final (para evitar erros na fórmula de pesquisa sem torná-la mais complexa)*. Além disso, supondo que sua lista de códigos de item esteja na ColunaA, exclua ColunasB:F antes de aplicar a fórmula de pesquisa acima (ou ajuste as referências para se adequar) - e adicione mais pesquisas, se necessário.
*Supervisão: certifique-se de que um ponto final seja adicionado em uma célula em cada coluna da tabela de pesquisa.
Adicione colunas para c5, c6 e c7 para permitir (a) comprimento máximo 7 com (b) cenário de 'pior caso' (ou seja, todos com um único caractere).
Responder3
Aqui está uma versão que usa a função de pesquisa (versão de find sem distinção entre maiúsculas e minúsculas). A configuração é a seguinte.
Na folha 1, os códigos a serem consultados iniciam-se na coluna A da Folha 1. O resultado final estará na coluna B. As colunas C, D, etc. possuem os códigos únicos dispostos horizontalmente na linha 1, ou seja, "BK" na C1, "R" em D2, etc. A maneira prática de conseguir isso é simplesmente copiar a lista de códigos na tabela de pesquisa e colar especial, transpô-los horizontalmente nas células C1, D1, etc.
Então, primeiro na célula B2, insira a seguinte fórmula:
=IF(NOT(ISERROR(SEARCH(C$1,$A2))),VLOOKUP(C$1,Sheet2!$A$2:$B$4,2,0),"")
Copie esta fórmula nas linhas da coluna C para quantas colunas de código você Ceed na linha 2.
Por fim, na célula C2, concatene todos os resultados da linha 2, ou seja, a fórmula
=D2&" "&E2&" "&F2
etc., para todas as colunas com códigos na linha 1. Esta etapa é tediosa, mas pode ser encurtada com a seguinte função VBA, que permite concatenar todas as células em um intervalo:
Function Concat(useThis As Range, Optional delim As String) As String
' this function will concatenate a range of cells and return the result as a single string
' useful when you have a large range of cells that you need to concatenate
' source: http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/
Dim retVal As String, dlm As String, cell As Range
retVal = ""
If delim = Null Then
dlm = ""
Else
dlm = delim
End If
For Each cell In useThis
If CStr(cell.Value) <> "" And CStr(cell.Value) <> " " Then
retVal = retVal & CStr(cell.Value) & dlm
End If
Next
If dlm <> "" Then
retVal = Left(retVal, Len(retVal) - Len(dlm))
End If
Concat = retVal
End Function
Você inseriria e copiaria esta função em um módulo no Developer VBA. O uso é simples - concat(C1:D1," "), por exemplo.
Observe que esta abordagem funciona para todos os códigos de 2 caracteres e para todos os códigos de 1 caractere se não estiverem nos códigos de 2+ caracteres, ou seja, se não houver pares de códigos como "R" e "BR".