I have a table where it needs to be repeated whenever there is a value 1 in A column D2 number of times as shown

I have tried with =QUERY(A1:D7,"select C,B where A=1")

Prefer to have a single long formula that can accomplish.

CodePudding user response：

use:

=ARRAYFORMULA(SORT(SPLIT(FLATTEN(FILTER(C2:C6&"×"&
 SEQUENCE(1, D2)&"×"&B2:B6*SEQUENCE(1, D2), A2:A6=1)), "×"), 2, 1))