Concatena todas as células que correspondem aos critérios (Excel)

Question

Eu tenho uma planilha como a seguinte:

O resultado esperado é todas as células começando com texto específico concatenado em ordem com o número de aparências.

A formatação como na imagem não é essencial, mas preferível. No entanto, o número (1, 2, 3) na frente de cada parte é muito importante.

Eu tentei usar SUMIF e CONCAT para referir-se a tabelas de arco-íris e processos de várias etapas, mas todos acabaram ficando fora de controle, lentos e não confiáveis.

Se possível, gostaria de fazê-lo sem macros / UDFs, pois o administrador do sistema não gosta deles, mas se não houver outra maneira, tudo bem.

Qualquer ajuda é apreciada!

    

Answer 1

Sugiro uma solução baseada em um pouco de código VBA e na função TEXTJOIN do Excel 2016. Observe que isso funcionará no Excel 2016 e acima, mas não funcionará em versões inferiores. Nesse caso, você terá que criar o UDF para o TEXTJOIN. Refiro-me à sua tag Excel 2016.

Neste exemplo, os dados de amostra estão nas células E4: E15.

Pressione ALT + F11 para abrir o Editor VBA. Insira um módulo no menu Inserir e insira o seguinte código nele.

Function GetString(a1 As String) As String

Dim array1 As Variant
Dim mystring As String

array1 = Split(a1, "|")

For i = LBound(array1) To UBound(array1)

    mystring = mystring & i + 1 & "." & array1(i) & " "

Next i

GetString = mystring

End Function

Salve o arquivo do Excel como uma planilha Excel .XLSM Macro.

Agora, em F4, por exemplo, coloque a seguinte fórmula e pressione CTRL + SHIFT + ENTER para criar e Fórmula da Matriz. A fórmula deve ser incluída em chaves para indicar que é uma fórmula de matriz.

=GetString(TEXTJOIN("|",TRUE,IF(LEFT(E4:E14,3)="fob",RIGHT(E4:E14,LEN(E4:E14)-LEN("fob")),"")))

Agora você recebe sua string como desejado.

Limitação - Observe que aqui estou usando o caractere de pipe "|" como Delimitador. Portanto, em casos raros, se o seu próprio Texto tiver um pipe, os resultados podem não ser os esperados. Nesse caso, use um desses caracteres que podem ser obtidos usando ALT + pad numérico, e. ALT + 789, e. §

CasovocêprefiraumaabordagemdefórmulasdoExcel,sugiroabaixoasoluçãobaseadaemumacolunaauxiliar.

Nesteexemplo,osdadosdeamostraestãonascélulasB3:B13.Agora,emC3,coloqueaseguintefórmulaepressioneCTRL+SHIFT+ENTERparacriarumafórmuladematriz.Agoraarraste-oparaaslinhaspretendidas.

=IFERROR(INDEX($B$3:$B$13,SMALL(IF(LEFT($B$3:$B$13,3)="fob",ROW($B$3:$B$13)-2,""),ROW(A1))),"") 

Observe o -2 aqui. Isso é importante, ele define o valor Index para começar com 1 com base na linha relativa na qual sua lista começa.

Esta fórmula cria uma lista contagiante de linhas obrigatórias da sua coluna original.

Agora, em D3, coloque a seguinte fórmula e pressione CTRL + SHIFT + ENTER na barra de fórmulas para criar uma fórmula de matriz. / p>

=TEXTJOIN(",",TRUE,IF(LEN(C3:C13)=0,"",ROW(C3:C13)-2&"."&RIGHT(C3:C13,LEN(C3:C13)-LEN("fob")))) 

Agora você deve pegar sua string desejada em D3. Veja a imagem abaixo.