Search code examples
google-sheetsarray-formulas

How to repeat cell value X times in google sheet using arrayformula?

enter image description here

Hi everyone,

I want to repeat the text in cell A3:A5 for X times where X = value in cell B3:B5. The out put should start from cell A8. Cell A8:A21 in the screenshot above is the expected output. May I know how should I achieve this by using ARRAYFORMULA? Any help will be greatly appreciated!

Solution

  • Place this formula in A8:

    =ArrayFormula(QUERY(FLATTEN(SPLIT(REPT(A3:A5&"|",B3:B5),"|",1,1)),"Select * WHERE Col1 Is Not Null"))

    REPT will repeat each value in A3:A5 with an appended pipe symbol ("|") however many times are in the corresponding cells B3:B5. So if A3 is "Exercise 1" and B3 is 3, REPT will produce Exercise 1|Exercise 1|Exercise 1 for that row, etc.

    SPLIT will then split each of those strings apart at the "|" symbol.

    FLATTEN will form one column of all of the above.

    QUERY will eliminate any null (i.e, "blank") results in the list, so that only non-null values are left.

    UPDATE

    Using new functionality made available since the original post date:

    =ArrayFormula(TOCOL(SPLIT(REPT(A3:A5&"|",B3:B5),"|"), 1))