Analisa uma célula e retorna valores associados em outra célula

Question

No Excel 2016, estou tentando escrever uma fórmula de matriz ou uma função vba para concluir o seguinte:

1.) O usuário insere uma string (um nome de projeto) em uma célula (célula D1 no exemplo anexo)

2.) As Células na Coluna B preencherão com o (s) número (s) associado (s) à string do Nome do Projeto com base no conteúdo da Coluna A, cada um separado por vírgulas

A coluna A contém algum número de entradas delimitadas por vírgulas. Cada entrada contém um Nome do Projeto e um Número associado separado por um caractere %

Por exemplo, a primeira linha da imagem de exemplo contém três entradas:

Project1 1234
Project2 2345
Project3 5678

Isso é inserido como "Projeto1% 1234, Projeto2% 2345, Projeto3% 5678" na célula

Anexei uma imagem de exemplo que contém uma versão em branco do meu problema e abaixo disso está o resultado desejado se o usuário inserisse Project1 . Espero que isso ilustre bem o problema, mas, se não, posso esclarecer.

Isso é possível com uma fórmula de matriz ou isso exigirá uma função definida pelo usuário em vba?

    

Answer 1

Sim, isso é possível apenas com uma fórmula de matriz. Não é exatamente simples, no entanto.

Matrizentra(Ctrl+Desloca+Enter)aseguintefórmulaemB2ecopia-cola/fill-downacoluna:

{=TEXTJOIN(",",TRUE,LEFT(TRIM(MID(SUBSTITUTE(A2,$D$1&"%",REPT(" ",LEN(A2))),(ROW(INDEX(B:B,1):INDEX(B:B,LEN(A2)/2))-1)*LEN(A2)+1,LEN(A2))),IFERROR(FIND(",",TRIM(MID(SUBSTITUTE(","&A2&",",$D$1&"%",REPT(" ",LEN(A2))),(ROW(INDEX(B:B,1):INDEX(B:B,LEN(A2)/2))-1)*LEN(A2)+1,LEN(A2))))-1,0)))}

A fórmula prettificada é a seguinte:

{=
TEXTJOIN(
  ",",
  TRUE,
  LEFT(
    TRIM(MID(SUBSTITUTE(A2,$D$1&"%",REPT(" ",LEN(A2))),(ROW(INDEX(B:B,1):INDEX(B:B,LEN(A2)/2))-1)*LEN(A2)+1,LEN(A2))),
    IFERROR(
      FIND(
        ",",
        TRIM(MID(SUBSTITUTE(","&A2&",",$D$1&"%",REPT(" ",LEN(A2))),(ROW(INDEX(B:B,1):INDEX(B:B,LEN(A2)/2))-1)*LEN(A2)+1,LEN(A2)))
      )-1,
      0
    )
  )
)}

Explicação:

A fórmula funciona substituindo o nome do projeto mais o separador na cadeia de destino por uma longa seqüência de espaços e depois "dividindo" a cadeia em uma matriz de blocos, cada um começando com o número do projeto de um projeto correspondente . Essa é a saída das funções TRIM(…) .

Em seguida, a parte mais à esquerda de cada bloco até, mas não incluindo, a primeira vírgula, ou seja, o número do projeto, é extraída.

Finalmente, os números do projeto são concatenados com a função TEXTJOIN() .

A parte IFERROR de IFERROR(FIND(",",TRIM(…))-1,0) é necessária para remover os erros retornados quando a função FIND() falha, com as sequências nulas resultantes sendo ignoradas por TEXTJOIN() .

Notas:

• A fórmula prettificada realmente funciona se inserida.

Answer 2

Como não vi a resposta do robinCTS no tempo acabei escrevendo uma função vba que resolve esse problema. Eu imaginei que eu iria compartilhá-lo, mesmo que seja um pouco desajeitado apenas no caso de ajudar alguém com o mesmo problema.

Function ParseK(celltxt As String, userin As String) As String
Dim project_name As String
Dim number As String
Dim final_result As String
Dim string_array() As String
ReDim string_array(5)
Dim i As Variant
string_array = Split(celltxt, ",")
For Each i In string_array
    number = Right(i, Len(i) - InStr(i, "%"))
    project_name = Left(i, InStr(i, "%") - 1)
    If InStr(project_name, userin) > 0 Or project_name = userin Then final_result = final_result & number & ","

Next i
ParseK = final_result
End Function