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.

 

[Domenic]

Make sure that you have the calculation mode set to automatic...

Ribbon >> Formulas >> Calculation >> Calculation >> Calculation options >> Automatic

 

[JPMAHONY]

Make sure that you have the calculation mode set to automatic...

Ribbon >> Formulas >> Calculation >> Calculation >> Calculation options >> Automatic

Hi, calculation is set to automatic and cell format is set to general.

 

[Eric W]

I too have encountered problems with TEXTJOIN not working consistently. I know my formula is correct, because like you, it works when I press F2 and Enter. I've never been able to quantify the problem, it seems to happen more often in some cases than others. I've kind of chalked it up to some kind of subtle situational and/or environmental bug in the TEXTJOIN logic. It is one of the newer functions.

As far as what to do about it, I'm not sure. At one point I wrote up a UDF that mimics the functionality of TEXTJOIN, called TEXTJOINX. If you want to try it, here's the code:

Function TextJoinX(sep As String, ign As Boolean, ParamArray SubArr() As Variant) As String
Dim i As Long, y As Variant
' Clone of TextJoin for versions of Excel prior to 2016
' 2016, Eric W, adapted from AConcat by Harlan Grove


    Application.Volatile
    TextJoinX = ""
    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 & sep & y.Value
                End If
            Next y
        ElseIf IsArray(SubArr(i)) Then
            For Each y In SubArr(i)
                If y = "" And ign Then
                Else
                    TextJoinX = TextJoinX & sep & y
                End If
            Next y
        Else
            If SubArr(i) = "" And ign Then
            Else
                TextJoinX = TextJoinX & sep & SubArr(i)
            End If
        End If
    Next i
    
    TextJoinX = Mid(TextJoinX, Len(sep) + 1)
        
End Function

Then you'd have to change your formulas to use that instead.

 

[JPMAHONY]

Eric, you sir are a wonderful man.

I too have encountered problems with TEXTJOIN not working consistently. I know my formula is correct, because like you, it works when I press F2 and Enter. I've never been able to quantify the problem, it seems to happen more often in some cases than others. I've kind of chalked it up to some kind of subtle situational and/or environmental bug in the TEXTJOIN logic. It is one of the newer functions.

As far as what to do about it, I'm not sure. At one point I wrote up a UDF that mimics the functionality of TEXTJOIN, called TEXTJOINX. If you want to try it, here's the code:

Function TextJoinX(sep As String, ign As Boolean, ParamArray SubArr() As Variant) As String
Dim i As Long, y As Variant
' Clone of TextJoin for versions of Excel prior to 2016
' 2016, Eric W, adapted from AConcat by Harlan Grove


    Application.Volatile
    TextJoinX = ""
    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 & sep & y.Value
                End If
            Next y
        ElseIf IsArray(SubArr(i)) Then
            For Each y In SubArr(i)
                If y = "" And ign Then
                Else
                    TextJoinX = TextJoinX & sep & y
                End If
            Next y
        Else
            If SubArr(i) = "" And ign Then
            Else
                TextJoinX = TextJoinX & sep & SubArr(i)
            End If
        End If
    Next i
    
    TextJoinX = Mid(TextJoinX, Len(sep) + 1)
        
End Function

Then you'd have to change your formulas to use that instead.

 

[Eric W]

Happy to help.

 

[johnnyL]

Old thread here, but it was referenced today so rather than posting in the thread that it was referenced, I decided to post directly here.

@Eric W I have ran a few tests on your TextJoinX (alternative code) and it seems to be excellent! I did however encounter one problem when using it as a direct replacement for 'TextJoin'.

I am sure it has to be something that I did not do correctly because other than one instance, it seems foolproof.

Book1
	A	B	C	D	E	F	G
1	This is a test of TEXTJOINx.					Works !
2
3	Check True with	Blank Cell	In the middle	Check True with Blank Cell In the middle		Works !
4	Check False with		In the middle	Check False with In the middle		Works!
5
6	A3	B3	A3, B3, A4, B4, A5, B5			Works !
7	A4	B4
8	A5	B5
9
10	Jane	Erin	Gordon	Ms.	Ms. Jane Erin Gordon	Works !
11
12	12/9/2021	12/16/2021	12/09/2021 - 12/16/2021			Works !
13
14	New York City	NY	10101	US	#VALUE!	<--- Not Work
15	end
16	,	,	,	;
17
Sheet3

Cell Formulas
Range	Formula
A1	A1	=TEXTJOINx(" ",TRUE, "This", "is", "a", "test", "of", "TEXTJOINx.")
D3	D3	=TEXTJOINx(" ",1,A3:C3)
D4	D4	=TEXTJOINx(" ",0,A4:C4)
C6	C6	=TEXTJOINx(", ", 1, A6:B8)
E10	E10	=TEXTJOINx(" ",1,D10,A10:C10)
C12	C12	=TEXTJOINx(" - ",1,TEXT(A12,"m/dd/yyyy"),TEXT(B12,"m/dd/yyyy"))
E14	E14	=TEXTJOINx(A16:D16, TRUE, A14:D15)

The last test listed doesn't seem to work. Did I mess something up?

 

[Eric W]

My UDF clone is only designed to use one delimiter. I wasn't aware that the original TEXTJOIN can use more. I can try to figure something out.

 

[Eric W]

It about doubled the size, and it's VERY lightly tested, but this works on your examples:

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) + 1)
        c = 0
        For Each y In sep
            separr(c) = y
            c = c + 1
        Next y
    Else
        ReDim separr(0 To 0)
        separr(0) = sep
    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

 

[johnnyL]

It about doubled the size, and it's VERY lightly tested, but this works on your examples:

I'm sorry.

I did run some more tests today with your new/improved code version.

Today, I only found two more examples, the last two examples, that do not seem to yield the proper response. I can't test the actual 'TextJoin' function to compare, I only have posted online results to compare to.

TextJoinAlternative.xlsm
	A	B	C	D	E	F	G
1	This is a test of TEXTJOINx.					Works !
2
3	Check True with	Blank Cell	In the middle	Check True with Blank Cell In the middle		Works !
4	Check False with		In the middle	Check False with In the middle		Works!
5
6	A6	B6	A6, B6, A7, B7			Works !
7	A7	B7
8
9	Jane	Erin	Gordon	Ms.	Ms. Jane Erin Gordon	Works !
10
11	12/9/2021	12/16/2021	12/09/2021 - 12/16/2021			Works !
12
13	New York City	NY	10101	US	New York City,NY,10101,US;end	Works !
14	end
15	,	,	,	;
16
17	John	2	Team 1	Jim, Bob		Works !
18	Jim	1	Team 2	John, Bill		Works !
19	Bob	1
20	Bill	2
21
22	Jim	Pens	Bob	Chalk, Crayons		Works !
23	Jim	Pencils	Jim	Pens, Pencils, Paper		Works !
24	Bob	Chalk
25	Jim	Paper
26	Bob	Crayons
27
28	Monkey	yeknoM				Works !
29
30	A30	B30	A30-- A31-- B30-- B31			Works !
31	A31	B31
32
33	New York City	NY	New York City NY	<--- works when cell is set to wrap text		Works !
34
35	,	TRUE	Red	Black	Red, Green, Blue, Black, White	Works !
36			Green	White
37			Blue
38
39	US	New York City	NY	10101	US, New York CityNY, 10101	? Not Sure, 2 commas?
40
41	1	2	3	#VALUE!	<--- should be 123	Not Work
42
Sheet3

Cell Formulas
Range	Formula
A1	A1	=TextJoinx(" ",TRUE, "This", "is", "a", "test", "of", "TEXTJOINx.")
D3	D3	=TextJoinx(" ",1,A3:C3)
D4	D4	=TextJoinx(" ",0,A4:C4)
C6	C6	=TextJoinx(", ", 1, A6:B7)
E9	E9	=TextJoinx(" ",1,D9,A9:C9)
C11	C11	=TextJoinx(" - ",1,TEXT(A11,"m/dd/yyyy"),TEXT(B11,"m/dd/yyyy"))
E13	E13	=TextJoinx(A15:D15, TRUE, A13:D14)
D17	D17	=TextJoinx(", ", TRUE, IF($B$17:$B$20=1, $A$17:$A$20, ""))
D18	D18	=TextJoinx(", ", TRUE, IF($B$17:$B$20=2, $A$17:$A$20, ""))
D22:D23	D22	=TextJoinx(", ", TRUE, IF($A$22:$A$26=C22, $B$22:$B$26, ""))
B28	B28	=TextJoinx("",1,MID(A28,{10,9,8,7,6,5,4,3,2,1},1))
C30	C30	=TextJoinx("-- ", 1, A30:A31,B30:B31)
C33	C33	=TextJoinx(CHAR(10), TRUE, A33:B33)
E35	E35	=TextJoinx(A35,B35,C35:C37,D35:D37)
E39	E39	=TextJoinx({", "}, TRUE, A39:D39)
D41	D41	=TextJoinx(,TRUE,A41:C41)
Press CTRL+SHIFT+ENTER to enter array formulas.