ARRAY FORMULA (TEXTJOIN) periodically not updating or returning correct values

[JPMAHONY]

Hi,

I am using a TEXTJOIN function in an ARRAY FORMULA to return multiple matches to one cell.

However periodically the formula doesnt update and/or doesnt update correctly.

See formula in the attached - cell reference - D63

If you play with the inputs in cells L37:L39 - you will see that periodically they are not updating.

I have multiple sheets (per employee) and need to ensure that formula's are updated automatically.

At the moment, i need to press F2+enter to update

Can you please suggest a solution.

I have basic VBA knowledge.

Any help greatly appreciated.

 

[Eric W]

This version handles those 2 cases as well:

Function TextJoinX(sep As Variant, ign As Boolean, ParamArray SubArr() As Variant) As String
Dim i As Long, y As Variant, c As Long, separr() As String, sc As Long, f1 As Boolean
' Clone of TextJoin for versions of Excel prior to 2016
' 2016, Eric W, adapted from AConcat by Harlan Grove
' 2021, Added multiple separator support

    Application.Volatile
    TextJoinX = ""
    
    If TypeOf sep Is Range Then
        ReDim separr(0 To sep.Cells.Count - 1)
        c = 0
        For Each y In sep
            separr(c) = y.Value
            c = c + 1
        Next y
    ElseIf IsArray(sep) Then
        ReDim separr(0 To UBound(sep) - LBound(sep))
        c = 0
        For Each y In sep
            separr(c) = y
            c = c + 1
        Next y
    Else
        ReDim separr(0 To 0)
        If IsMissing(sep) Then
            separr(0) = ""
        Else
            separr(0) = sep
        End If
    End If
    c = 0
    sc = UBound(separr) + 1
    f1 = False
    
    For i = LBound(SubArr) To UBound(SubArr)
        If TypeOf SubArr(i) Is Range Then
            For Each y In SubArr(i).Cells
                If y = "" And ign Then
                Else
                    TextJoinX = TextJoinX & IIf(f1, separr(c Mod sc), "") & y.Value
                    c = IIf(f1, c + 1, c)
                    f1 = True
                End If
            Next y
        ElseIf IsArray(SubArr(i)) Then
            For Each y In SubArr(i)
                If y = "" And ign Then
                Else
                    TextJoinX = TextJoinX & IIf(f1, separr(c Mod sc), "") & y
                    c = IIf(f1, c + 1, c)
                    f1 = True
                End If
            Next y
        Else
            If SubArr(i) = "" And ign Then
            Else
                TextJoinX = TextJoinX & IIf(f1, separr(c Mod sc), "") & SubArr(i)
                c = IIf(f1, c + 1, c)
                f1 = True
            End If
        End If
    Next i
        
End Function

Be aware that there will likely be other discrepancies between this and the original TEXTJOIN, simply because the code is different, and I have no access to the original.

 

[johnnyL]

Excellent Job, as usual @Eric W! Thank You.