Mesclar a quantidade variável de linhas no Excel

Question

Eu tenho uma planilha enorme com a coluna A contendo o nome de uma pessoa e a coluna B contendo os cargos da pessoa em questão, assim:

--------------------------------------------
| Column A       | Column B                |
|-------------------------------------------
| John Doe       | CEO                     |
|                | CIO                     |
|                | CTO                     |
| Frank Black    | General Manager         |
|                | HR Manager              |
| Tabitha White  | Payroll specialist      |
| Jane Doe       | General Supervisor      |
|                | Superintendent          |
|                | Building maintenance    |
|                | System administrator    |
|                | IT specialist           |
--------------------------------------------

Meu objetivo é mesclar os cargos para uma única linha em sua própria coluna C , separados por vírgula. No entanto, cada pessoa pode ter entre um a dez títulos e eu tenho milhares de pessoas na planilha.

Trabalhar com cada pessoa separadamente está fora de questão, por isso estou procurando a maneira mais eficiente (ou mais fácil) de conseguir isso:

------------------------------------------------------------------------------------------------------------------------------------------
| Column A       | Column B              | Column C                                                                                      |
|----------------------------------------------------------------------------------------------------------------------------------------|
| John Doe       | CEO                   | CEO, CIO, CTO                                                                                 |
|                | CIO                   |                                                                                               |
|                | CTO                   |                                                                                               |
| Frank Black    | General Manager       | General Manager, HR Manager                                                                   |
|                | HR Manager            |                                                                                               |
| Tabitha White  | Payroll specialist    | Payroll specialist                                                                            |
| Jane Doe       | General Supervisor    | General Supervisor, Superintendent, Building Maintenance, System administrator, IT specialist |
|                | Superintendent        |                                                                                               |
|                | Building maintenance  |                                                                                               |
|                | System administrator  |                                                                                               |
|                | IT specialist         |                                                                                               |
------------------------------------------------------------------------------------------------------------------------------------------

Até agora não tive sorte em tentar duplicar fórmulas com muita eficiência.

    

Answer 1

Eu não sei como fazer isso com a fórmula, mas se está tudo bem em usar o VBA, tente algo assim:

Sub newMacro()
    Dim i As Integer
    Dim posName As Integer
    Dim val As String

    val = Cells(1, 2).Value
    posName = 1

    For i = 2 To 1200
        If IsEmpty(Cells(i, 1)) Then
            val = val + "," + Cells(i, 2).Value
        Else
            Cells(posName, 3).Value = val

            val = Cells(i, 2).Value
            posName = i
        End If
    Next
End Sub

A macro não está otimizada, mas isso deve funcionar. Em Por apenas altere o valor de 1200 para o número de linhas que deseja processar.

    

Answer 2

Uma solução bastante desajeitada:

Use uma coluna de trabalho, por exemplo Coluna D .

Na célula D1 tem a seguinte fórmula:

=IF(A1="",
IF(A2="",IF(D2="",B1,CONCATENATE(B1,", ",D2)),B1),
IF(A2="",IF(D2="",B1,CONCATENATE(B1,", ",D2)),B1)
)

Tenha a seguinte fórmula na célula C1 :

=IF(A1="","",D1)

Copie a fórmula das colunas para que todas as referências respectivas funcionem.

Eu suspeito que há coisas a ver com tabelas dinâmicas que fazem as coisas de uma forma mais clara - não é algo com o que eu já joguei.