How to combine arrays into one array without VBA?

[Opaquer]

Hey guys

So, at work I'm working on a stock take type program, and I've *almost* got it working, but hit a snag. The spreadsheet I have at the moment ends up working out a bunch of stuff, and at the end, spits out a huge array of text. The issue I'm facing is that the array is dynamic (using references like A2# to get the array regardless of the size etc), and it's causing a few issues. I end up with a certain number of columns (which I can work out, but let's say 3 for now). I want to have another column at the end that combines all of the text in that row, preferably with a comma between them.

So, for example, in A1, B1 and C1 I have "Hello", "World", "Test". In D1, I want something that spits out "Hello,World,Test" by the end of it. The issue is that our version of Excel is all web based, so I can't use VBA or anything for it.

Does anyone have any ideas on how I can make this work? Concat doesn't work, as when I use concat(A1#), it makes one cell with all the values in column A, then column B, then C. I also can't seem to get indirect to work with the dynamic ranges - any time I use # in a reference for indirect, it spits out an error

If you have any questions please don't hesitate to ask!

Thanks in advanced!

 

[StephenCrump]

Welcome to the Forum!

Have you looked at the TEXTJOIN function?

 

[Opaquer]

Welcome to the Forum!

Have you looked at the TEXTJOIN function?

Thank you!

So, I definitely have, but I also tried like 18 other things today and forgot what each one did, and I'm not at work so can't refresh myself in things, so bear with me as I try remember what happened

I forgot to mention this in my previous post, but the final array is actually longer than what I mentioned - it then gets filtered down once more to the appropriate number of columns. That said, I don't think that makes a difference here

I feel like I got close with textjoin, but didn't quite get there. If I'm remembering right, I think I managed to get it working with a specific range I typed in and indirect, but then couldn't get it working with the # notation (is there a better word for this by the way? I feel like # notation isn't the right one ). Am I right in saying that when using the # notation it did the same sort of thing as concat and would put all of column A first, then all of column B then all of column C? Or did I do something wrong with it?

 

[StephenCrump]

It sounds like TEXTJOIN might work for you. You can certainly FILTER/SORT and TEXTJOIN the resulting array.

Perhaps you can post what you're actually trying to do, rather than an over-simplified version?

It's best if you can use the XL2BB add-in to do this: XL2BB - Excel Range to BBCode

Then we can see and easily copy your screenshot and formulae, like this:

	A	B	C	D	E	F
1	q	w	e	r	t	y
2
3	q	w	e	r	t	y
4
5	q,w,e,r,t,y
Sheet1

Cell Formulas
Range	Formula
A3:F3	A3	=A1:F1
A5	A5	=TEXTJOIN(",",,A3#)
Dynamic array formulas.

 

[Opaquer]

It sounds like TEXTJOIN might work for you. You can certainly FILTER/SORT and TEXTJOIN the resulting array.

Perhaps you can post what you're actually trying to do, rather than an over-simplified version?

It's best if you can use the XL2BB add-in to do this: XL2BB - Excel Range to BBCode

Then we can see and easily copy your screenshot and formulae, like this:

	A	B	C	D	E	F
1	q	w	e	r	t	y
2
3	q	w	e	r	t	y
4
5	q,w,e,r,t,y
Sheet1

Cell Formulas
Range	Formula
A3:F3	A3	=A1:F1
A5	A5	=TEXTJOIN(",",,A3#)
Dynamic array formulas.

I don't think I can get XL2BB on the work computer - everything is monitored and we only have access to the Web version of Excel anyway?

Thanks for uploading the spreadsheet - I'll try get a photo tomorrow at work. What I'm trying to do is to get a list of stock kept in particular locations in the store. Long story short, I'll end up with varying numbers of columns based on which store its at, and each column has various numbers of rows too. For example, once filtered and whatnot, my store could have 3 columns, A, B and C, each with 200 rows, all contained within a # notation, so choosing A1# selects them all. However, I want each row in column D to show the corresponding value of that row of column A, B and C, with a delimiter in between. Preferably I'd have this in # notation so I can adjust it for however many products there are in the store.

For example, without getting into exact products and whatnot, imagine in column A I have "a", "b", "c", "d" and so on up to z, for a total of 26 rows. Column B can be A, B, C, D etc - the same thing in upper case. And column C might have all the numbers from 1 to 26. Using A1# highlights all of these as one array. What I'd want is to have D1 be aA1, D2 to be bB2, D3 to be cC3 and so on to zZ26. As I said, I'll get a photo tomorrow to help out with it.

Will using TEXTJOIN like you described work? Or is there something else I need to use or do?

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D
1
2	496356	145056	SU963450	496356, 145056, SU963450
3	530128	177905	TQ301779	530128, 177905, TQ301779
4	377540	164614	ST775646	377540, 164614, ST775646
5	292711	93732	SX927937	292711, 93732, SX927937
6	507132	222843	TL071228	507132, 222843, TL071228
7	538096	191679	TQ380916	538096, 191679, TQ380916
8	435204	BC	NZ352565	435204, BC, NZ352565
9	531697	106316	TQ316063	531697, 106316, TQ316063
10	433619	424133	SE336241	433619, 424133, SE336241
11
Master

Cell Formulas
Range	Formula
A2:C10	A2	=Pcode!E2:G10
D2:D10	D2	=LET(Txt,"|"&TEXTJOIN("|",,A2#)&"|",cols,COLUMNS(A2#),Qty,SEQUENCE(((LEN(Txt)-LEN(SUBSTITUTE(Txt,"|","")))-1)/cols,,,cols),SUBSTITUTE(REPLACE(LEFT(Txt,FIND("~",SUBSTITUTE(Txt,"|","~",Qty+cols))-1),1,FIND("~",SUBSTITUTE(Txt,"|","~",Qty)),""),"|",", "))
Dynamic array formulas.

 

[Opaquer]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D
1
2	496356	145056	SU963450	496356, 145056, SU963450
3	530128	177905	TQ301779	530128, 177905, TQ301779
4	377540	164614	ST775646	377540, 164614, ST775646
5	292711	93732	SX927937	292711, 93732, SX927937
6	507132	222843	TL071228	507132, 222843, TL071228
7	538096	191679	TQ380916	538096, 191679, TQ380916
8	435204	BC	NZ352565	435204, BC, NZ352565
9	531697	106316	TQ316063	531697, 106316, TQ316063
10	433619	424133	SE336241	433619, 424133, SE336241
11
Master

Cell Formulas
Range	Formula
A2:C10	A2	=Pcode!E2:G10
D2:D10	D2	=LET(Txt,"|"&TEXTJOIN("|",,A2#)&"|",cols,COLUMNS(A2#),Qty,SEQUENCE(((LEN(Txt)-LEN(SUBSTITUTE(Txt,"|","")))-1)/cols,,,cols),SUBSTITUTE(REPLACE(LEFT(Txt,FIND("~",SUBSTITUTE(Txt,"|","~",Qty+cols))-1),1,FIND("~",SUBSTITUTE(Txt,"|","~",Qty)),""),"|",", "))
Dynamic array formulas.

Thanks for the welcome!

And thanks for that Fluff - that's done it! I've gotta say, that's a very, very nice formula you have there! I found a slight issue where sometimes cells were empty, so tweaked a few things (like making the textjoin not ignore empty cells, and a few other tweaks to handle those and dealing with extra commas etc), but after a bit of working through it, I managed to get it working perfectly! Thank you so much for your help - this is exactly what I was after!

Thanks again!

 

[Fluff]

Glad we could help & thanks for the feedback.