Sheets formula to transpose, split and repeat multiple columns

1
votes

I'm looking for a script that will transpose, split and repeat multiple columns by a value in another cell.

I have it working for one column but wondered if it possible to do it for multiple columns:

=TRANSPOSE(SPLIT(REPT(C2 & ",",A2),",",false,true))

Working sheet:

https://docs.google.com/spreadsheets/d/1W5kFTNF6g0DI1W_lHJJU91CBwL_YnEiLLb_NYH7GkLQ/edit?usp=sharing

2
google-sheetssplitgoogle-sheets-formulatransposearray-formulas
Your desired output has duplicate products with absolutely nothing unique about them, is that supposed to make sense or be useful somehow? – CodeCamper

2 Answers

0
votes

or try this shorter one:

=ARRAYFORMULA({TRANSPOSE(SPLIT(CONCATENATE(REPT(B2:B&",", A2:A^2)), ",")), 
               TRANSPOSE(SPLIT(CONCATENATE(REPT(C2:C&",", A2:A)),   ","))})

0

0
votes

The following code will output exactly as you asked.

=arrayformula({transpose(split(concatenate(rept(B2:B4 & ",",A2:A4^2)),",",false,true)),transpose(split(concatenate(REPT(C2:C4 & ",",A2:A4)),",",false,true))})