Backwards compatibility for excel 2010. Replacing TEXTJOIN with CONCATENATE

[jbarrick007]

I am trying to convert a TEXTJOIN to CONCATENATE. I have done so successfully on a few cells but this one is giving me fits. Any help would be appreciated because I just keep coming up with #SPILL errors.
The thing different with this cell compared to the others is the Filter at the end of the formula. Maybe it is just needing a different set of Parentheses.
Any help is apricated.

Currently:
=IF(ISNUMBER(FIND(".",A5)),TEXTJOIN(CHAR(10),TRUE,"#"&TEXT([@[Workflow '# ]],"#0.00")&" "&[@[Milestone Name ]]," - Duration: "&(TEXT([@Start],"m/d/yy"))&" ("&[@Duration]&")",FILTER(I5:I$1000,(LEFT(B5:B$1000,LEN(A5)+1)=A5&".")*ISNUMBER(SEARCH("x",BE5:BE$1000))," • No Assigned Tasks")),"")

The issue i have had so far is even just switching the textjoin to concatenate, without the delimitator the results from the filter wont have a line break between them.

 

[jasonb75]

What you are trying will only work with office 365, you can not concatenate arrays or use the filter function in old versions of excel.

The only way that you might get it to work is with a udf such as Aconcat, however that would still need some significant changes to allow for the filtering.

 

[jbarrick007]

these dumb limitations of older versions. thank goodness we at least have progress. unfortunately the company still has some older software.

Hopefully I can figure something else out.

 

[jbarrick007]

I have spent a lot of time on this sheet if anyone else can help me with other functions instead of the filter or textjoin that will work on older years of excel it would be fantastic

 

[mole999]

these dumb limitations of older versions. thank goodness we at least have progress. unfortunately the company still has some older software.

Hopefully I can figure something else out.

new software is always designed to do something the old can't the old last longer because it never knew anything about future products

 

[jasonb75]

if anyone else can help me with other functions instead of the filter or textjoin that will work on older years of excel it would be fantastic

Perhaps if you explained what you were trying to do then we might be able to find a method that will work, using a formula that doesn't work doesn't tell us anything.
Regardless of what you are trying to do, it is unlikely that it will be possible without using vba, so if that is not an option then you're going to be out of luck.

 

[jbarrick007]

Unfortunately my XL2BB isn't working it just keeps locking excel up if i try to generate output it will only do table only output
I Tried to start a new threw since my problem is actually the Filter function but is was closed already as a duplicate thread.

As you can see the last column was using the formula below to gather and combine various data. If this can not be done on 2010 fine, but hopefully at least 2019 can do it.
Currently:
=IF(ISNUMBER(FIND(".",A5)),TEXTJOIN(CHAR(10),TRUE,"#"&TEXT([@[Workflow '# ]],"#0.00")&" "&[@[Milestone Name ]]," - Duration: "&(TEXT([@Start],"m/d/yy"))&" ("&[@Duration]&")",FILTER(I5:I$1000,(LEFT(B5:B$1000,LEN(A5)+1)=A5&".")*ISNUMBER(SEARCH("x",BE5:BE$1000))," • No Assigned Tasks")),"")

1.01	1.01.00		,[Sch''d Start]],MATCH([@[Workflow '# ]],Planning_Calculator[[#All],[Workflow '#]],0)),"")]5/19/20	1 wk	,[Sch''d End]],MATCH([@[Workflow '# ]],Planning_Calculator[[#All],[Workflow '#]],0)),"")]5/23/20		| FACILITY EVALUATION VERIFICATION						#1.01 | FACILITY EVALUATION VERIFICATION - Duration: 5/19/20 (1 wk) • Confirm the DC-9 and PRY have been verified by FRP
	1.01.01							• Confirm the DC-9 and PRY have been verified by FRP
1.02	1.02.00	1.1	,[Sch''d Start]],MATCH([@[Workflow '# ]],Planning_Calculator[[#All],[Workflow '#]],0)),"")]5/19/20	1 wk	,[Sch''d End]],MATCH([@[Workflow '# ]],Planning_Calculator[[#All],[Workflow '#]],0)),"")]5/23/20		| INFORM INVESTIGATION						#1.02 | INFORM INVESTIGATION - Duration: 5/19/20 (1 wk) • Send investigation meeting assignment (FRINV) to CF through APR • Send FRINV assignment to FRP through APR and notify oversight
	1.02.01							• Send investigation meeting assignment (FRINV) to CF through ARP
	1.02.02							• Send FRINV assignment to FRP through ARP and notify oversight

 

[jasonb75]

Not sure what is happening with XL2BB but it looks like the table references are messing things up, could you do copy the same range to a blank sheet, then pastespecial - values so that it posts clearer please?

edit:- I assume that A5 in the formula refers to 1.01 in the top left corner of the table, but it would be helpful if you could include headers with the sample to aid in identification of the table names when you repost the example.

 

[jbarrick007]

yes sorry I have tried deleting and reinstalling XL2BB 3 times and it still isn't working for some reason. It will copy as a table but will not generate output with all other info without causing excel to completely lock up.

In the planning tab Column is where the I have the formula with TEXTJOIN and FILTER but need to find a different way (hopefully) that works on previous versions than 365. Otherwise it is a lot of manual work IIl have to do. I know I also have to change TEXTJOIN to CONCATENATE, but that is a much easier task.

Currently in N5:
=IF(ISNUMBER(FIND(".",A5)),TEXTJOIN(CHAR(10),TRUE,"#"&TEXT([@[Workflow '# ]],"#0.00")&" "&[@[Milestone Name ]]," - Duration: "&(TEXT([@Start],"m/d/yy"))&" ("&[@Duration]&")",FILTER(I5:I$1000,(LEFT(B5:B$1000,LEN(A5)+1)=A5&".")*ISNUMBER(SEARCH("x",BE5:BE$1000))," • No Assigned Tasks")),"")

	A	B	C	D	E	F	G	H	I	N
	Workflow #	Workflow Order	PWA #	Start	Duration	Finish	Phase Name	Milestone Name	Key Milestone Tasks	Planning TAB
5	1.01​	1.01.00		5/19/20​	1 wk	5/23/20​		| FACILITY EVALUATION VERIFICATION		#1.01 | FACILITY EVALUATION VERIFICATION - Duration: 5/19/20 (1 wk) • Confirm the DC-9 and PRY have been verified by FRP
6		1.01.01							• Confirm the DC-9 and PRY have been verified by FRP
7	1.02​	1.02.00	1.1​	5/19/20​	1 wk	5/23/20​		| INFORM INVESTIGATION		#1.02 | INFORM INVESTIGATION - Duration: 5/19/20 (1 wk) • Send investigation meeting assignment (FRINV) to CF through APR • Send FRINV assignment to FRP through APR and notify oversight
8		1.02.01							• Send investigation meeting assignment (FRINV) to CF through APR
9		1.02.02							• Send FRINV assignment to FRP through APR and notify oversight

 

[jasonb75]

I know I also have to change TEXTJOIN to CONCATENATE, but that is a much easier task.

Even working around the filter function, concatenate will not work with the final part of your formula. Concatenate does not accept arrays of any kind, you would have to list each cell individually. The amount of data that you have would exceed the limits of the function.

I've managed to get the formula working in 2019 without the filter function, although not with the "No assigned tasks" alternative when there are no rows with the relevant workflow # and an x in column BE.

As I said earlier, what you want will not be possible without the use of vba but I'm not going to look into that until you confirm whether or not that is an acceptable method.