Hi all,
Chat GPT couldn't crack it (or I'm not good at prompts) but I'm sure you guys can (I've seen it happen before!).
I've got dynamic spilled array something like this:
| 1;2 | a |
| 3 | b |
and I'm looking for a dynamic array formula to turn it into:
| 1 | a |
| 2 | a |
| 3 | b |
i.e., the first column contains semicolon-delimited values and they need to be split into multiple rows (each (;-delim) value - new row) and those (split) rows should retain value from the 2nd column, that's why {"1;2", "a"} becomes two rows {"1","a";"2","a"} but the 2nd row stays {"3","b"} (as the "3" isn't delimited) and gets VSTACKed to the 1st.
The point is that the original array is dynamic in terms of rows and how many values there are to delimit. The column count however is static (well, there will actually be 3 cols - the 3rd would also retain it's value - exactly as column 2).
Help, anybody?
I would use Power Query to do this. Split by delimited and then in the Advanced settings choose 'by rows'.
PQ is a valid approach but I'd still prefer a dynamic array - it's more mind-bending for one + it works without any 'mechanical' refreshes needed (and hence also in Excel Online).
Therefore - if anyone is still up for the challenge - I'm all ears!
I'm curious, this dynamic spilled array, is the number of elements in a cell limited to 2 or can there be more than 2?
Perhaps something like this - just amend the D1# to whatever your spilled range should be:
=LET(rng,D1#,
numRows,SEQUENCE(ROWS(rng)),
a,REDUCE("",numRows,LAMBDA(s,c,VSTACK(s,LET(t,TEXTSPLIT(INDEX(rng,c,1),,";"),HSTACK(t,DROP(CHOOSEROWS(rng,SEQUENCE(ROWS(t),,c,0)),,1)))))),
DROP(a,1))
Looks great. pity my 2021 version runs short on the new 365 functions ;(
@Velouria - I can tell a genius when I see one!!
Yet again you've hit it out of the park - thank you so much - exactly what was need!!
