带有标准的 xl2010/xl2013 的 TEXTJOIN [英] TEXTJOIN for xl2010/xl2013 with criteria
问题描述
我有 2 个工作表.第一个工作表有大约 100 行,但我们只对 Y 列感兴趣. Y 列中的单元格混合了空白单元格 ("")、文本和数字以及显示 #N/A 的单元格.与图片类似,但数据集更大.
在第二个工作表中,有一个单元格,我想用文本和数字"捕获单元格,并在同一单元格内的不同行中显示每个记录(例如,如果 100 个单元格中有 12 个使用文本和数字",然后我想在第二个工作表的特定单元格中显示此信息.像这样:
我尝试过类似的操作,但它似乎只捕获了第一行文本(例如标题行):
=IFERROR(INDEX('1Comms'!Y:Y,MATCH(TRUE,'1Comms'!Y:Y<>"",0)),"")
有没有办法也错过标题?
我做错了什么,有没有办法做到这一点?
这个 TextJoinIfs 用户定义函数(又名 UDF)提供了基本的
<小时>
非常感谢 Lorem Ipsum Generator 提供示例字符串内容.>
I have 2 worksheets. The 1st worksheet has about 100 rows but we are only interested column Y. The cells in column Y has a mixture of blank cells (""), text and numbers, and cells that displays #N/A. Similar to the picture but with a bigger data-set.
In the 2nd worksheet, there is a cell that I would like to capture the cells with 'texts and numbers' and display it each record in a different line within the same cell (e.g. if there were 12 out of a 100 cells with 'texts and numbers', then I would like to display this information in a particular cell in the 2nd worksheet. Like this:
I have tried something like this but it seems to only capture the 1st row of text only (e.g. the title row):
=IFERROR(INDEX('1Comms'!Y:Y,MATCH(TRUE,'1Comms'!Y:Y<>"",0)),"")
Is there a way to miss off the title as well?
What am I doing wrong and is there a way to do this?
This TextJoinIfs user-defined-function (aka UDF) provides basic TEXTJOIN functionality to Excel 2003 - 2013 versions as well as expanded functionality for all versions by adding optional error control, uniqueness, sorting and a paramarray of conditions for easy criteria.
This TextJoinIfs UDF code belongs in a public module code sheet; e.g. Book1 - Module1 (code).
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(<delimiter>, <options>, <header_rows>, <string_range>, [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(<delimiter>, <options>, <header_rows>, <string_range>, [criteria_range1, criteria1], [criteria_range2, criteria2], …)
Simple TextJoin operation discarding blanks and errors, keeping only unique strings. Concatenated with a line feed (vbLF) delimiter but ignoring the first two header rows and sorted ascending.
=textjoinifs(CHAR(10), 24, 2, A:A)
Expanded TextJoinIfs operation discarding blanks and errors, keeping only unique strings. Concatenated with a semi-colon/space delimiter. One condition set of range and criteria.
=textjoinifs("; ", 8, 0, B:B, A:A, A2)
Expanded TextJoinIfs operation discarding blanks and errors. Concatenated with a comma/space delimiter. Multiple condition pairs using maths comparisons.
=textjoinifs(", ", 0, 0, B:B, A:A, ">="&D2, A:A, "<="&E2)
Many thanks to the Lorem Ipsum Generator for the sample string content.
这篇关于带有标准的 xl2010/xl2013 的 TEXTJOIN的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!