Como adicionar conteúdo de várias linhas em uma célula, agrupando por uma terceira linha no Excel 2010

Question

Estou tentando obter vários nomes de linhas diferentes em uma célula em uma linha, mas agrupá-los por um valor em outra coluna. Eu também preciso da lista de nomes para ter quebras de linha entre eles, não uma lista separada por vírgula. Não tenho certeza se isso vai ser possível. Eu tenho algumas das partes que eu preciso, como = CONCATENATE (TRANSPOSE (B2: B19)) para obter os dados em uma célula, e char (10) para adicionar a quebra de linha, mas eu não tenho sido capaz juntar tudo para conseguir o que eu quero.

Os dados atualmente são assim:

Oqueeuquero:

Até mesmo uma solução VBA é ok - embora não seja meu strong. ;) Eu preciso dos dados como este para usar em uma mala direta do Word.

Além disso, observe que há várias outras colunas de dados na planilha. Deixei de fora por simplicidade.

    

Answer 1

Após postar, e antes de ver a outra resposta, trabalhei com um colega de trabalho e conseguimos trabalhar.

Começou por classificar por grupo e depois por nome.

Em seguida, adicionamos uma coluna que verificaria ambas para ver se o nome já havia sido adicionado (há duplicatas) e se o grupo era igual ou diferente. Se esse nome não foi adicionado e ainda era o mesmo grupo, ele concatenou o novo nome na lista da célula acima, usando:

=IF(B2=B1,C1,IF(A2=A1,CONCATENATE(C1,CHAR(10),B2),B2))

Em outra coluna, criamos uma contagem inversa dos nomes no grupo.

=IF(A2=A1,D1-1,COUNTIF(A:A,A2))

Que tem isso:

Emseguida,apenasfiltramosacolunaDpor"1":

    

Answer 2

Sugiro uma solução que requer pouco de VBA.

Neste exemplo, os dados de amostra estão em B2: C10. Deixe E1 como célula de cabeçalho e, em E2, coloque a seguinte fórmula e pressione CTRL + SHIFT + ENTER de dentro da barra de fórmulas para criar uma fórmula de matriz. A fórmula agora deve estar entre chaves para indicar que é uma fórmula de matriz.

=IFERROR(INDEX($B$2:$B$10, MATCH(0, COUNTIF(E$1:$E1, $B$2:$B$10), 0),1),"") 

Arraste isto até você obter espaços em branco. Isso primeiro cria uma lista de valores exclusivos do grupo em B2: B10. Observe que, onde quer que você coloque essa fórmula, pelo menos uma célula acima dela deve estar disponível para ser mencionada. E1, neste caso, como a fórmula começa em E2.

Vamos usar uma função chamada TEXTJOIN. No entanto, na maioria das versões do Excel, isso não está disponível. Você pode tê-lo no caso de estar usando a versão Office 365 do Excel 2016. Se não estiver disponível, use abaixo de UDF (User Defined Function) no VBA para replicar a mesma funcionalidade.

Pressione ALT + F11 para acessar o VBA Editor. Inserir um módulo no menu Inserir. Coloque o seguinte UDF nele.

Function TEXTJOIN1(delimiter As String, ignore_empty As Boolean, ParamArray cell_ar() As Variant)
For Each cellrng In cell_ar
    For Each cell In cellrng
        If ignore_empty = False Then
            result = result & cell & delimiter
        Else
            If cell <> "" Then
                result = result & cell & delimiter
            End If
        End If
    Next cell
Next cellrng
TEXTJOIN1 = Left(result, Len(result) - Len(delimiter))
End Function

Agora, de volta à planilha do Excel, usaremos essa função como uma UDF na fórmula. Em F2, coloque a seguinte fórmula e pressione CTRL + SHIFT + ENTER para criar uma fórmula de matriz.

=IFERROR(TEXTJOIN1(CHAR(10),TRUE,IF($B$2:$B$10=E2,$C$2:$C$10,"")),"")

Arraste até as linhas pretendidas. Espere, isso cria uma lista de nomes por grupo concatenado por Char (10), mas para ver o efeito correto você precisa ativar o Wrap Text nas células pretendidas. Você pode fazê-lo manualmente a partir da opção Formatar células no Excel ou você pode usar essa macro abaixo simples para fazer isso por você. Basta especificar o intervalo no começo. Neste exemplo, é E2: F4.

Pressione ALT + F11 para acessar o VBA Editor. Insira um módulo no menu Inserir e depois o seguinte código nele. Isso cria uma macro chamada Format1

Sub Format1 ()

    Range("E2:F4").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

Voltar à planilha do Excel Pressione ALT + F8 para acessar a caixa de diálogo Macro e executar o Format1.

Teste esta solução ao seu lado e me avise em caso de problemas.

    

Answer 3

PrivateSubMergeDuplicates()DimRngAsRange,xCellAsRangeDimxRowsAsIntegerxTitleId="Merge Duplicates"

Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)

Application.ScreenUpdating = False
Application.DisplayAlerts = False

xRows = WorkRng.Rows.count

For Each Rng In WorkRng.Columns
    For i = 1 To xRows - 1
        For j = i + 1 To xRows
            If Rng.Cells(i, 1).Value <> Rng.Cells(j, 1).Value Then
                Exit For
            End If
        Next
        WorkRng.Parent.Range(Rng.Cells(i, 1), Rng.Cells(j - 1, 1)).Merge
        i = j - 1

    Next
Next

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

N.B. Insira este código como Module e volte à planilha para executá-lo, selecione o intervalo de dados necessário quando INPUT BOX aparecer e finalize com Ok.