CODEWITHSUNDEEP
excelexcel-formulaexcel-lambda
JimminyJim
JimminyJim

Reputation: 146

VSTACK and REDUCE(?) to expand column

I'm trying to

  1. expand a column with MAKEARRAY, using instances to determine number of rows that should be populated with a value and then
  2. using REDUCE to VSTACK multiple of these arrays together

I seem to be having trouble accessing the corresponding index of instances to be able to determine the size of the MAKEARRAY for each, and seems to be using the number of elements instead of the value of each index.

enter image description here

=LET(
instances,B2:B4,
letters,A2:A4,
DROP(REDUCE("",letters,LAMBDA(acc,curr,VSTACK(acc,
MAKEARRAY(1+instances,1,LAMBDA(r,c,curr))))),1))

Edit: Note that I'm specifically trying to avoid the character limit of TEXTJOIN, so if used as an outer function, it would be an issue.

Upvotes: -1

Views: 169

Answers (1)

Black cat
Black cat

Reputation: 6177

The formula in K8:

=TEXTSPLIT(TEXTJOIN("|",TRUE,REPT(K1:K3&"|",L1:L3+1)),,"|",TRUE)

enter image description here

With the original ranges

=TEXTSPLIT(TEXTJOIN("|",TRUE,REPT(A2:A4&"|",B2:B4+1)),,"|",TRUE)

Upvotes: -1

Related Questions