single-formula combining of multiple dynamic arrays (VSTACK?)

PriestZoth

New Member
Joined
Jan 20, 2024
Messages
2
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
Hello there!
(this is my first post, so I expect to fail at being clear.. apologies in advance! I also will write inelegantly and breach the formatting etiquette: I apologise for that too)

I have a problem with creating a single formula that produces a single dynamic array which combines multiple dynamic arrays.

For example, i have a range of four cells stacked vertically A2:A5.
Each cell contains text that is sometimes separated by ALT+Enter / Unichar(10) in two or more lines. In total, there are 8 lines split unevenly across 4 cells.
I want to achieve a range of cells, stacked vertically, in which each single line in the original four cells A2:A5 is stored individually in its own cell: in the example I'm using, I should get 8 cells in a vertical range.

I would like to have this done with a single formula.
Ideally, for each cell cell, the formula should create an array (let's call it a sub-array) of one or more cells (depend on how many lines each cell contains).
The final result of the formula should be all the sub-arrays combined into a single dynamic array.

I thought of getting to it first step by step, then merging each step back into a single formula.
First, easy enough, I created single independent formulas, using TEXTSPLIT on each cell referenced individually (i.e. A2, A3, A4, A5); I spaced these formulas out appropriately (to avoid the #SPILL! error) in Column C
Excel Formula:
 =TEXTSPLIT(A2, , UNICHAR(10))
Excel Formula:
 =TEXTSPLIT(A3, , UNICHAR(10))
Excel Formula:
 =TEXTSPLIT(A4, , UNICHAR(10))
Excel Formula:
 =TEXTSPLIT(A5, , UNICHAR(10))

Each of the four formulas above produces a single or multi cell array, exactly as expected.
Then I can merge each of these invididual arrays using VSTACK in the formula below
Excel Formula:
 =VSTACK(C2#, C3#, C6#, C8#)

This works: I get an array of 8 lines.
(I tried to show this in the attached "screenshot.png").

I thought I could merge the components of this two-steps solution with the formula below, in which
-- I change the cell references in teh TEXTSPLIT function from single cells to the array, and then
-- nest the TEXTSPLIT function into the VSTACK function.

Excel Formula:
=VSTACK(   TEXTSPLIT([B]A2:A5[/B], , UNICHAR(10))   )

Needless to say, no joy! This produces an array of only four cells, showing each only the first line of each of the original cells A2:A5.
Please see the screenshot here below, showing on the left the output dynamic array, and on the right the fomula it is based on.
1705763418335.png


I guess I'm approaching this from the wrong direction..
What is the right approach to this?

(I tried something with MAKEARRAY, but the LAMBDA is something that I still don't understand, so I cannot even tell whether that's a route I should go down on)
(I tried to use the mini-sheet add-in, but could not get it to work.. I'll get there, in time)

Thank you all!
 

Attachments

  • Screenshot.png
    Screenshot.png
    29.4 KB · Views: 25

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
How about
Excel Formula:
=REDUCE(TEXTSPLIT(A2,,CHAR(10)),A3:A5,LAMBDA(x,y,VSTACK(x,TEXTSPLIT(y,,CHAR(10)))))
 
Upvote 0
Solution
Hi Fluff..
well, that is spot on what I wanted.
Not that I understand (yet) how it works..
All my talk of myself being good with Excel feels quite senseless, now.. but ehi, at least I've got some homework for the weekend, and two functions to study.
(I'm kinda glad that I was already somewhat aware -- at a stretch -- that the LAMBDA would be part of the solution)
Thank you so much for this
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
In this particular instance this would also work (if you wanted to avoid the reduce + lambda complexity).

Excel Formula:
=TEXTSPLIT(TEXTJOIN(CHAR(10),TRUE,A2:A5),,CHAR(10)

- Joins all the text with char(10) and then splits of char(10). For anything more complex it probs wouldn't work.

Found this when trying to understand how to use the reduce/lambda piece for my own problem (that I haven't solved).....
 
Upvote 0

Forum statistics

Threads
1,223,952
Messages
6,175,596
Members
452,657
Latest member
giadungthienduyen

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
Back
Top