How to combine arrays into one array without VBA?

Opaquer

New Member
Joined
Jul 6, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Web
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!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,251
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Forum!

Have you looked at the TEXTJOIN function?
 

Opaquer

New Member
Joined
Jul 6, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Web
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 :p

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 :p). 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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,251
Office Version
  1. 365
Platform
  1. Windows
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:

ABCDEF
1qwerty
2
3qwerty
4
5q,w,e,r,t,y
Sheet1
Cell Formulas
RangeFormula
A3:F3A3=A1:F1
A5A5=TEXTJOIN(",",,A3#)
Dynamic array formulas.
 

Opaquer

New Member
Joined
Jul 6, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Web

ADVERTISEMENT

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:

ABCDEF
1qwerty
2
3qwerty
4
5q,w,e,r,t,y
Sheet1
Cell Formulas
RangeFormula
A3:F3A3=A1:F1
A5A5=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,658
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
+Fluff 1.xlsm
ABCD
1
2496356145056SU963450496356, 145056, SU963450
3530128177905TQ301779530128, 177905, TQ301779
4377540164614ST775646377540, 164614, ST775646
529271193732SX927937292711, 93732, SX927937
6507132222843TL071228507132, 222843, TL071228
7538096191679TQ380916538096, 191679, TQ380916
8435204BCNZ352565435204, BC, NZ352565
9531697106316TQ316063531697, 106316, TQ316063
10433619424133SE336241433619, 424133, SE336241
11
Master
Cell Formulas
RangeFormula
A2:C10A2=Pcode!E2:G10
D2:D10D2=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.
 
Solution

Opaquer

New Member
Joined
Jul 6, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Web
Hi & welcome to MrExcel.
How about
+Fluff 1.xlsm
ABCD
1
2496356145056SU963450496356, 145056, SU963450
3530128177905TQ301779530128, 177905, TQ301779
4377540164614ST775646377540, 164614, ST775646
529271193732SX927937292711, 93732, SX927937
6507132222843TL071228507132, 222843, TL071228
7538096191679TQ380916538096, 191679, TQ380916
8435204BCNZ352565435204, BC, NZ352565
9531697106316TQ316063531697, 106316, TQ316063
10433619424133SE336241433619, 424133, SE336241
11
Master
Cell Formulas
RangeFormula
A2:C10A2=Pcode!E2:G10
D2:D10D2=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,658
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,141,857
Messages
5,709,027
Members
421,605
Latest member
Danny_G

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top