Utilizing a function like TextJoin without office 365?

Question

I have headers in A1:C1 and have values (X) in A2:C5. Range can vary, but I want Column D to spit out the header values delimited by a / whenever an X is found in the range.

impmented this vba function because I have an older version of excel without textjoin integrated:

Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
    Dim d As Long
    Dim c As Long
    Dim arr2()
    Dim t As Long, y As Long
    t = -1
    y = -1
    If TypeName(arr) = "Range" Then
        arr2 = arr.Value
    Else
        arr2 = arr
    End If
    On Error Resume Next
    t = UBound(arr2, 2)
    y = UBound(arr2, 1)
    On Error GoTo 0

    If t >= 0 And y >= 0 Then
        For c = LBound(arr2, 1) To UBound(arr2, 1)
            For d = LBound(arr2, 1) To UBound(arr2, 2)
                If arr2(c, d) <> "" Or Not skipblank Then
                    TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
                End If
            Next d
        Next c
    Else
        For c = LBound(arr2) To UBound(arr2)
            If arr2(c) <> "" Or Not skipblank Then
                TEXTJOIN = TEXTJOIN & arr2(c) & delim
            End If
        Next c
    End If
    TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
End Function

and this formula given by Scott in order to get the headers into an array:

=TEXTJOIN("/",TRUE,IF(A2:C2="X",$A$1:$C$1,"")) after pressing ctrl+shift+enter

how can I omit the #value! errors? I tried wrapping an iferror statement, but that's now just populating nothing in the cells.

There are several questions here on SO with code for a TEXTJOIN udf. Which one are you using? — BigBen, Sep 26 '19 at 17:24
See my answer: https://stackoverflow.com/questions/39532189/vlookup-with-multiple-criteria-returning-values-in-one-cell for a udf — Scott Craner, Sep 26 '19 at 17:24
And the formula would be simpler: `=TEXTJOIN("/",TRUE,IF(A2:C2="x",$A$1:$C$1,""))` — Scott Craner, Sep 26 '19 at 17:26
and another: https://stackoverflow.com/questions/43104790/concatenate-column-headers-if-value-in-rows-below-is-non-blank — Scott Craner, Sep 26 '19 at 17:27
And different approach: https://stackoverflow.com/questions/56858571/merge-values-of-column-b-based-on-common-values-on-column-a/56858767 — Scott Craner, Sep 26 '19 at 17:29
@ScottCraner this works, but how do I omit a `#value` error when there are no x's in the range? — NidenK, Sep 26 '19 at 17:29
If it is spitting out `#NAME` then you do not have the code in a general module attached to the workbook. — Scott Craner, Sep 26 '19 at 17:29
It returns `#value` or is it interspersed with the correct values? — Scott Craner, Sep 26 '19 at 17:31
@I added in the cod in a general module, no more `#name?` errors, but I am getting a `#value` error when all the cells between columns A:C are empty in that row. — NidenK, Sep 26 '19 at 17:31
Try: `=IFERROR(TEXTJOIN("/",TRUE,IF(A2:C2="x",$A$1:$C$1,"")),"")` — Scott Craner, Sep 26 '19 at 17:32
@ScottCraner I tried the first link, and it worked, just displayed the value error when no values existed in that row. I just tried you comment above, and I initially tried to wrap an iferror statement, but it doesn't spit out anything anymore — NidenK, Sep 26 '19 at 17:34
[Edit] your original post with the code you used and show some screen shots of your data and output. — Scott Craner, Sep 26 '19 at 17:40
By wrapping in IFERROR like my last statement I get a blank on the row without x's but get the correct output on the others. Can you show what happens when you do it. Add another picture. — Scott Craner, Sep 26 '19 at 17:50
@ScottCraner that's so weird, it's working now. Thanks a bunch. Can you provide an answer so I can properly give you credit? — NidenK, Sep 26 '19 at 17:54
As this now is a duplicate of those linked I will let them stand as the answer. — Scott Craner, Sep 26 '19 at 17:55

score 0 · Answer 1 · answered Feb 17 '22 at 10:54

Function TextJoin(delimiter As String, ignore_empty As Boolean, ParamArray PAitems() As Variant) As String
 'like TextJoin in office 365
 If ignore_empty Then
  TextJoin = JoinIE(delimiter, PAitems)
 Else
  TextJoin = JoinKE(delimiter, PAitems)
 End If
End Function

Function JoinIE(delimiter As String, ParamArray PAitems() As Variant) As String
 'join ignore empty
 Dim v, w
 Dim s As String
 Dim j As String
 For Each v In PAitems
  If IsArray(v) Then
   For Each w In v
    j = JoinIE(delimiter, w)
    If Len(j) Then s = s & j & delimiter
   Next
   If Len(s) >= Len(delimiter) Then s = Left(s, Len(s) - Len(delimiter))
   v = s
   s = vbNullString
  End If
  If Not IsMissing(v) And Not IsError(v) Then
   If Len(v) Then JoinIE = JoinIE & v & delimiter
  End If
 Next
 If Len(JoinIE) <= Len(delimiter) Then Exit Function
 JoinIE = Left(JoinIE, Len(JoinIE) - Len(delimiter))
End Function

Function JoinKE(delimiter As String, ParamArray PAitems() As Variant) As String
 'join keep empty
 Dim v, w
 Dim s As String
 For Each v In PAitems
  If IsArray(v) Then
   For Each w In v
    s = s & JoinKE(delimiter, w) & delimiter
   Next
   If Len(s) >= Len(delimiter) Then s = Left(s, Len(s) - Len(delimiter))
   v = s
   s = vbNullString
  End If
  If IsMissing(v) Or IsError(v) Then v = Empty
  JoinKE = JoinKE & v & delimiter
 Next
 If Len(JoinKE) <= Len(delimiter) Then Exit Function
 JoinKE = Left(JoinKE, Len(JoinKE) - Len(delimiter))
End Function

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Feb 17 '22 at 13:29

Utilizing a function like TextJoin without office 365?

1 Answers1