TEXTJOIN для xl2010 / xl2013 с критериями

У меня есть 2 листа. 1-й рабочий лист имеет около 100 строк, но нам интересен только столбец Y. Ячейки в столбце Y имеют смесь пустых ячеек («»), текст и цифры и ячейки, которые показывают # N / A. Подобно изображению, но с большим набором данных.

Столбец L

Во 2-м листе есть ячейка, в которой я хотел бы захватить ячейки «текстами и цифрами» и отображать каждую запись в другой строке внутри одной ячейки (например, если было 12 из 100 ячеек с «текстами» и числа “, то я хотел бы отобразить эту информацию в определенной ячейке во втором листе. Например:

введите описание изображения здесь

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

=IFERROR(INDEX('1Comms'!Y:Y,MATCH(TRUE,'1Comms'!Y:Y"",0)),"") 

Есть ли способ упустить название?

Что я делаю неправильно, и есть ли способ сделать это?

Эта пользовательская функция TextJoinIfs (aka UDF) предоставляет базовые функциональные возможности TEXTJOIN для версий Excel 2003 – 2013, а также расширенные функциональные возможности для всех версий путем добавления дополнительного контроля ошибок, уникальности, сортировки и параметрирования условий для простых критериев.

Этот код TextJoinIfs UDF принадлежит к общедоступному коду модуля модуля; например Book1 – Module1 (код).

 Option Explicit Public Function TextJoinIfs(delim As String, iOptions As Long, iIgnoreHeaderRows As Long, _ rng As Range, ParamArray pairs()) As Variant 'TEXTJOINIFS - Basic TEXTJOIN functionality for XL2003-XL2013 versions ' Expanded TEXTJOINIFS functionality for all versions ' =TextJoinIfs(, , , , [criteria_range1, criteria1], [criteria_range2, criteria2], …) ' OPTIONS ' +2 Include blanks ' +4 Include worksheet errrors ' +8 Unique list ' +16 Sort ascending (cannot be used with 17) ' +17 Sort descending (cannot be used with 16) If Not CBool(UBound(pairs) Mod 2) Then TextJoinIfs = CVErr(xlErrValue) Exit Function End If Dim i As Long, j As Long, a As Long, arr As Variant Dim bIncludeBlanks As Boolean, bIncludeErrors As Boolean, bUniqueList As Boolean Dim bSorted As Boolean, bDescending As Boolean bIncludeBlanks = CBool(2 And iOptions) bIncludeErrors = CBool(4 And iOptions) bUniqueList = CBool(8 And iOptions) bSorted = CBool(16 And iOptions) bDescending = CBool(1 And iOptions) Set rng = Intersect(rng, rng.Parent.UsedRange.Offset(iIgnoreHeaderRows - rng.Parent.UsedRange.Rows(1).Row + 1, 0)) With rng ReDim arr(.Cells.Count) If Not IsMissing(pairs) Then For i = LBound(pairs) To UBound(pairs) Step 2 Set pairs(i) = pairs(i).Resize(rng.Rows.Count, rng.Columns.Count).Offset(iIgnoreHeaderRows, 0) Next i End If For j = 1 To .Cells.Count If CBool(Len(.Cells(j).Text)) Or bIncludeBlanks Then If Not IsError(.Cells(j)) Or bIncludeErrors Then If IsError(Application.Match(.Cells(j).Text, arr, 0)) Or Not bUniqueList Then If IsMissing(pairs) Then arr(a) = .Cells(j).Text a = a + 1 Else For i = LBound(pairs) To UBound(pairs) Step 2 If Not CBool(Application.CountIfs(pairs(i).Cells(j), pairs(i + 1))) Then Exit For Next i If i > UBound(pairs) Then arr(a) = .Cells(j).Text a = a + 1 End If End If End If End If End If Next j End With ReDim Preserve arr(a - 1) If bSorted Then Dim tmp As String For i = LBound(arr) To UBound(arr) - 1 For j = i + 1 To UBound(arr) If CBool(LCase(CStr(arr(i))) < LCase(CStr(arr(j))) And bDescending) Xor _ CBool(LCase(CStr(arr(i))) > LCase(CStr(arr(j))) And Not bDescending) Then tmp = arr(j): arr(j) = arr(i): arr(i) = tmp End If Next j Next i End If TextJoinIfs = Join(arr, delim) End Function 

Синтаксис:

 =TextJoinIfs(, , , , [criteria_range1, criteria1], [criteria_range2, criteria2], …) 

Документация

введите описание изображения здесь

Пример 1

Операция Simple TextJoin отбрасывает пробелы и ошибки, сохраняя только уникальные строки. Объединение с разделителем строки (vbLF), но игнорирование первых двух строк заголовка и сортировка по возрастанию.

 =textjoinifs(CHAR(10), 24, 2, A:A) 

введите описание изображения здесь

Пример 2.

Расширенная операция TextJoinIfs отбрасывает пробелы и ошибки, сохраняя только уникальные строки. Конкатенация разделителем с запятой / пробелом. Один набор условий и критериев.

 =textjoinifs("; ", 8, 0, B:B, A:A, A2) 

введите описание изображения здесь

Пример 3.

Расширенная операция TextJoinIfs, отбрасывающая пробелы и ошибки. Конкатенация с разделителем запятой / пробелом. Множественные пары условий, использующие математические сравнения.

 =textjoinifs(", ", 0, 0, B:B, A:A, ">="&D2, A:A, "<="&E2) 

введите описание изображения здесь


Большое спасибо генератору Lorem Ipsum Generator для содержимого строки примера.

Давайте будем гением компьютера.