CODEWITHSUNDEEP
exceldynamic-arrays
Rui
Rui

Reputation: 3

Excel MS365: 2-D dynamic array creation problem

I have an Excel problem that boils down to the following:

Given the input range A1:B3 (a 2-D array), is it possible to use only formulas to create the output D1:K3? I would like to use the input values row-wise to create a range and then fill the output accordingly (e.g. {1,4} as an input means that positions 1 through 4 on that output's row should be filled). You can assume that the highest value in the input array determines the width of the output.

input array example (A1:B3) and desired output example (D1:K3)

input array example (A1:B3) and desired output example (D1:K3)

I was able to create an extremely convoluted solution with a SEQUENCE/SWITCH combination, but it only works for each row separately. I don't think it will help, but I can reproduce it here if needed.

Upvotes: 0

Views: 51

Answers (2)

andrewb
andrewb

Reputation: 1146

Perhaps the most straightforward way:

=IF(AND(COLUMN()-3 >= $A1;COLUMN()-3 <= $B1);1;0)

Simply put this formula in D1 and expand it.

Result: result

Upvotes: 0

Rory
Rory

Reputation: 34075

One way:

=LET(d,A1:B3,MAKEARRAY(ROWS(d),MAX(d),LAMBDA(r,c,AND(c>=INDEX(d,r,1),c<=INDEX(d,r,2))+0)))

Upvotes: 4

Related Questions