July 13, 2021
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?
Trusted Members
October 17, 2018
Trusted Members
Moderators
November 1, 2018
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))
Answers Post
1 Guest(s)