CODEWITHSUNDEEP
sortingfiltersplitgoogle-sheets-formulaflatten
Code Guy
Code Guy

Reputation: 3198

Sheet formula to repeat the cells

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")

https://docs.google.com/spreadsheets/d/1B4261XqXBbAQs8vHAyg40TeCvo8qFxZ9AIPezmN2yjc/copy

enter image description here

Prefer to have a single long formula that can accomplish.

Upvotes: 1

Views: 72

Answers (1)

player0
player0

Reputation: 1

use:

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

enter image description here

Upvotes: 1

Related Questions