Localizando vários valores codificados em uma string e retornando um valor de pesquisa para cada código encontrado

Question

Eu 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, detalhes de cor vermelha) e 600BKR-YEL (corpo preto, detalhes de cor vermelha, 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 ela significa:

BK    Black Body
R     Red Detail
YEL   Yellow Base 

Eu gostaria de escrever uma função que encontre todos os códigos no título do item que está na planilha de pesquisa e, em seguida, retorne 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?

    

Answer 1

Eu faria algo como o seguinte. Supondo que sua lista de códigos de itens esteja na coluna A da Folha1 e, digamos, nas linhas 2-20, e sua lista de pesquisa esteja nas colunas A e B da Folha2, linhas 2-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 ele coloque as pesquisas em uma única fórmula, essa abordagem seria muito trabalhosa se você tivesse muitos códigos. Eu recomendaria que você inserir linhas auxiliares na Sheet1 para cada posição de código (por exemplo, mid (A2,4,2) e, em seguida, concatená-los em uma única seqüência de caracteres.

    

Answer 2

A resposta curta é "Sim", uma maneira é mais longa.

Como existem muitas possibilidades não excluídas em 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 dependendo do item simples, etc.), sugiro que seja mais seguro. comece com a análise dos códigos. Isso deve evitar complicações com os gostos de GR, seja Gray Body com Red Detail (geralmente um esquema de cores compatível!) Ou Green Body.

Supondo que estas foram analisadas (em três colunas B: D iniciando na Linha 2) a partir de então, é fácil, com a sua tabela de consulta (a da folha separada) chamada codes :

=VLOOKUP(B2,codes,2,0)&VLOOKUP(C2,codes,2,0)&VLOOKUP(D2,codes,2,0)

na folha de códigos de item. Para obter espaços entre os valores, assumi que todas as entradas na coluna da direita da sua matriz de código terminam em um espaço (fácil de organizar, como com os itens = A2 & copiados, etc.).

Assim, a parte difícil pode ser analisar os códigos dos códigos dos itens, para os quais eu sugeriria adicionar colunas como abaixo (mais, se houver mais de três códigos):

comfórmulascomoabaixo:

ColumnB é definir onde começar a procurar códigos (no caso de códigos simples que não sejam três numéricos). Colunas C: D são para onde começar a procurar o próximo código / o comprimento do próximo código. Eu concordaria 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 uma análise bem-sucedida, sugiro Copiar / Colar especial / Valores (para se livrar das fórmulas) e substitua blank com um ponto final (para evitar que a fórmula de pesquisa seja eliminada sem torná-la mais complexa) * Além disso, supondo que a lista de códigos de itens esteja na Coluna A, exclua ColumnsB: F antes de aplicar a fórmula de pesquisa como acima (ou ajuste as referências de acordo) e adicione mais pesquisas, se necessário.

* Supervisão: verifique se um ponto final foi adicionado em uma célula em cada coluna da tabela de consulta.

Adicione colunas para c5, c6 e c7 para permitir (a) comprimento máximo 7 com o cenário (b) 'pior cenário' (ou seja, todos os caracteres únicos).

    

Answer 3

Aqui está uma versão que usa a função de pesquisa (versão sem distinção entre maiúsculas e minúsculas do find). A configuração é a seguinte.

Na planilha 1, os códigos a serem pesquisados começam na coluna A da Planilha 1. O resultado final estará na coluna B. As colunas C, D, etc. têm os códigos únicos organizados horizontalmente na linha 1, ou seja, " BK "em C1," R "em D2, etc. A maneira prática de conseguir isso é simplesmente copiar a lista de códigos na tabela de consulta e colar Transpose Special horizontalmente nas células C1, D1, etc.

Em seguida, primeiro na célula B2, digite a seguinte fórmula:

  =IF(NOT(ISERROR(SEARCH(C$1,$A2))),VLOOKUP(C$1,Sheet2!$A$2:$B$4,2,0),"")

Copie essa fórmula nas linhas da coluna C para quantas colunas de código você Ceated na linha 2.

Finalmente, 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. Essa etapa é tediosa, mas pode ser encurtada com a seguinte função VBA, que permite que todas as células em um intervalo sejam concatenadas:

  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ê poderia inserir e copiar essa função em um módulo no Developer VBA. O uso é simples - concat (C1: D1, ""), por exemplo.

Observe que essa abordagem funciona para todos os códigos de 2 caracteres e todos os códigos de 1 caractere, se eles não estiverem nos códigos de caractere 2+, ou seja, se não houver pares de código, como "R" e "BR" .