TEXTJOIN Alternative

[mightymo77]

Hey guys. I was reading through some posts and everyone here seems very helpful. I have this formula below, however it's not working on my work machine which uses Excel 2016. Can anyone create an alternative for me that will function in Excel 2016? I would appreciate any help!

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(FIND($F2:$T2,"BLACK,BLUE")),$F$1:$T$1,""))

 

[sanantonio]

That's a limit of Excel. See here:

Excel specifications and limits - Microsoft Support

In Excel 2010, the maximum worksheet size is 1,048,576 rows by 16,384 columns. In this article, find all workbook, worksheet, and feature specifications and limits.

support.microsoft.com

If you have a string longer than that, you'll have to break it up into pieces.

Any recommended suggestions on how best to break this into pieces?

Example of what I'm trying to accomplish is:

Populating this Product Group (PG) column with all the different product groups our vendors supply us with. The data is in here:

A data export listing all vendors and the articles they supply us with, along with the PG of that article. What I'm needing to do is take that and extract just the unique PGs for each vendor. The data length is searching tends to be 25000-40000 rows of data.

The current code I use is

=TEXTJOIN(", ",TRUE,IF(PGbyVendor!$E$1:$E$259=Vendors!D5,PGbyVendor!$J$1:$J$259,""))

Which does the job but it stops working if I extend either past 259 ?

Is there a way to make this work? Or an alternative method?

 

[Eric W]

Frankly, I don't think that putting so much data in a single cell is a good idea. A person looking at it wouldn't be able to find anything in a reasonable way, and any automated method would have to parse it out before it could find anything. If you're trying to export a list of PGs for a vendor, I'd just filter your sheet, then just export the visible rows.

 

[sanantonio]

There's a lot of data to search thru but if it's only returning uniques it will only return maybe 2-3 PGs at most for each Vendor.

For example:

But I just need it to search a larger area

 

[Eric W]

I can't explain why it stops at 259 for you. Here's an example where I go to 1000. Maybe you can try the F2 formula, using FILTER instead of IF.

Book1
	A	B	C	D	E	F
1	33	928448			21	597893, 509601, 419659, 265251, 72472, 461359, 408492, 774620, 862343, 537129, 627612, 89349, 87483, 117812
2	65	803582				597893, 509601, 419659, 265251, 72472, 461359, 408492, 774620, 862343, 537129, 627612, 89349, 87483, 117812
3	90	988767
4	37	664720
5	19	895427
Sheet27

Cell Formulas
Range	Formula
F1	F1	=TEXTJOIN(", ",TRUE,IF(A1:A1000=E1,B1:B1000,""))
F2	F2	=TEXTJOIN(", ",,FILTER(B1:B1000,A1:A1000=E1))

 

[Sulprobil]

Because that doesn't always work.

An example would have been helpful.
So far I just have examples of my function being superior to Excel's TEXTJOIN (also something I normally try to avoid because of compatibility reasons):

Confusion Of Join Method

I'm creating an app for user to select data but they don't have to select all the data. For example, they can choose to include some columns, but not the others. One thing that is known is the data selected will have the same rows(so if the user selects A1:A5 and C2:C5, then that's the user...

www.mrexcel.com

 

[johnnyL]

An example would have been helpful.
So far I just have examples of my function being superior to Excel's TEXTJOIN (also something I normally try to avoid because of compatibility reasons):

Confusion Of Join Method

I'm creating an app for user to select data but they don't have to select all the data. For example, they can choose to include some columns, but not the others. One thing that is known is the data selected will have the same rows(so if the user selects A1:A5 and C2:C5, then that's the user...

www.mrexcel.com

Here is a test I made for testing different versions of TextJoin Altenatives. Try it out and see how your version does. Let us know.

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

Cell Formulas
Range	Formula
E1	E1	=TextJoin(" ",TRUE, "This", "is", "a", "test", "of", "TEXTJOIN.")
E3	E3	=TextJoin(" ",1,A3:C3)
E4	E4	=TextJoin(" ",0,A4:C4)
E6	E6	=TextJoin(", ", 1, A6:B7)
E9	E9	=TextJoin(" ",1,D9,A9:C9)
E11	E11	=TextJoin(" - ",1,TEXT(A11,"m/dd/yyyy"),TEXT(B11,"m/dd/yyyy"))
E13	E13	=TextJoin(A15:D15, TRUE, A13:D14)
E17	E17	=TextJoin(", ", TRUE, IF($B$17:$B$20=1, $A$17:$A$20, ""))
E18	E18	=TextJoin(", ", TRUE, IF($B$17:$B$20=2, $A$17:$A$20, ""))
E22	E22	=TextJoin(", ", TRUE, IF($A$22:$A$26=C22, $B$22:$B$26, ""))
E28	E28	=TextJoin("",1,MID(A28,{10,9,8,7,6,5,4,3,2,1},1))
E30	E30	=TextJoin("-- ", 1, A30:A31,B30:B31)
E33	E33	=TextJoin(CHAR(10), TRUE, A33:B33)
E35	E35	=TextJoin(A35,B35,C35:C37,D35:D37)
E39	E39	=TextJoin({", "}, TRUE, A39:D39)
E41	E41	=TextJoin(,TRUE,A41:C41)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Sulprobil]

Here is a test I made for testing different versions of TextJoin Altenatives. Try it out and see how your version does. Let us know.

...

Thanks but I am not interested in dealing with pathological cases such as parameter Delimiter being an array or parameter Ignore_empty having an integer value.
I am taking away the case Text(i) being a String and not an array, though.

 

[johnnyL]

Your choice if you want to ignore problem points with the code that you submitted. You are the one who touted the code as 'superior' to Excel's TextJoin. I don't see how it can be viewed as 'superior' when it can't handle situations that Excel's TextJoin can handle. Opinions can vary though.