Объединить переменное количество строк в Excel

Question

У меня есть огромный лист с колонкой A содержащей имя человека, и колонкой B содержащей названия должностей для данного человека, например:

--------------------------------------------
| 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           |
--------------------------------------------

Моя цель - объединить названия должностей в одну строку в собственном столбце C , разделенном запятой. Тем не менее, каждый человек может иметь от одного до десяти названий, и у меня есть тысячи сотрудников на рабочем листе.

О работе с каждым человеком отдельно не может быть и речи, поэтому я ищу наиболее эффективный (или самый простой) способ добиться этого:

------------------------------------------------------------------------------------------------------------------------------------------
| 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         |                                                                                               |
------------------------------------------------------------------------------------------------------------------------------------------

До сих пор мне не повезло, пытаясь очень эффективно дублировать формулы.

Answer 1

Довольно неуклюжее решение:

Используйте рабочий столбец, например столбец D

В ячейке D1 есть следующая формула:

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

Имейте следующую формулу в ячейке C1:

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

Скопируйте формулу вниз по столбцам, чтобы все соответствующие ссылки работали.

Я подозреваю, что есть некоторые вещи, которые можно сделать с помощью сводных таблиц, которые делают вещи более четко, хотя я не играл с ними.

Answer 2

Я не знаю, как сделать это с формулой, но если это нормально, использовать VBA, попробуйте что-то вроде этого:

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

Макрос не оптимизирован, но это должно работать .. В поле For просто измените значение 1200 на номер строки, которую хотите обработать.