Соедините несколько «динамических» строк в одну ячейку

Question

У меня есть диапазон ячеек в столбцах A, которые нужно объединить в одну ячейку в столбце B, диапазон этих ячеек является динамическим. Как я могу объединить эти клетки? см. изображение для более подробного объяснения.

Мой сценарий аналогичен этому посту, но с небольшим отличием, разница в том, что пустая строка, которую нужно объединить, находится над строками, как показано на рисунке .

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

Формула имеет вид = IF(ISBLANK(B2), TEXTJOIN1(",", FALSE, OFFSET(B2, -(ROW()- MAX(IF(ISBLANK($ B $ 1:B1), ROW($ B $ 1:B1)), 0))- 1), 0):OFFSET(В2, -1,0)), "")

нужна помощь, чтобы исправить формулу, чтобы удовлетворить мой случай.

Answer 1

Вот ваше решение. В этом примере образец данных находится в ячейках B2:B17. Чтобы это решение работало, вы должны оставить одну пустую ячейку перед данными и после данных, т.е. в этом случае B1 и B18. Предполагается, что между диапазонами есть только одна пустая ячейка, иначе решение может работать некорректно.

Если ваша версия Excel не имеет функции TEXTJOIN, используйте ниже UDF в VBA для ее создания. Нажмите ALT + F11 для доступа к VBA Editor. Вставьте модуль из меню вставки. Дважды щелкните имя модуля в левой панели, чтобы открыть его редактор кода.

Вставьте в него следующий код.

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

Сохраните файл как Macro Enabled WOrkbook XLSM, если у вас Excel 2007 и выше.

В ячейку C1 поместите следующую формулу и перетащите ее вниз до длины данных столбца. Как уже упоминалось выше, первая и последняя ячейка должны быть пустыми.

=IF(ISBLANK(B1),TEXTJOIN1(",",FALSE,OFFSET(B1,1,0):OFFSET(B1,MIN(IF(ISBLANK(B2:$B$18),ROW(B2:$B$18)-ROW(B2),9^99)),0)),"")

Нажмите CTRL + SHIFT + ENTER в пределах панели формул, чтобы создать формулу массива. Формула должна быть заключена в фигурные скобки, чтобы указать, что это формула массива.

Теперь перетащите его вниз по длине столбца.