Google Sheets - Repeat values 'n' times from a range

2
votes

I am trying to execute the following in google sheets by repeating certain cell values from a range of cells (number of repetitions) and iterating it horizontally till the end of the row. Desired Output and current result

Formula used for current output:

={ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(QUERY(REPT(A1&",",A2:A4 ), ,999^99), ","))));ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(QUERY(REPT(C1&",",C2:C4 ), ,999^99), ","))));ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(QUERY(REPT(E1&",",E1:E4 ), ,999^99), ","))))}
1
google-sheetsgoogle-sheets-formula

1 Answers

2
votes

use:

=ARRAYFORMULA(FLATTEN(SPLIT(TEXTJOIN("×", 1, 
 REPT({A1&"×", C1&"×", E1&"×"}, {A2:A, C2:C, E2:E})), "×")))

enter image description here

shorter:

=INDEX(FLATTEN(SPLIT(TEXTJOIN("×", 1, 
 REPT({A1, C1, E1}&"×", {A2:A, C2:C, E2:E})), "×")))

update:

=INDEX(QUERY(FLATTEN(SPLIT(QUERY(FLATTEN(QUERY(FLATTEN(
 REPT("×"&{A1, C1, E1}&"×", {A2:A, C2:C, E2:E})),,9^9)),,9^9), "×")), 
 "where not Col1 starts with ' '"))

enter image description here