Search code examples
google-sheetsarray-formulas

ARRAYFORMULA with repetition

I have two columns of data, and would like to distribute the elements of one of these columns over several rows. I can easily calculate the index of the element I need, but cannot figure out how to access the element.

A    B   Desired output  Formula for index: =ARRAYFORMULA(IF(A:A,CEILING(ROW(A:A)/3+1),""))
1    11       22                  2
2    22       22                  2
3    33       22                  2
4    44       33                  3
5             33                  3
6             33                  3
7             44                  4

How can I modify my formula for the index so that it yields the item of column B at the calculated index?

I tried =ARRAYFORMULA(IF(A:A, INDEX(B:B, CEILING(ROW(A:A)/3+1), 1), "")) but that only repeats the first element (22) 7 times.

Solution

  • Use Vlookup instead of Index:

    =ARRAYFORMULA(IF(A:A,vlookup(CEILING(ROW(A:A)/3+1),A:B,2),""))

    enter image description here

    EDIT

    It isn't necessary to use a key column, you could use something like this:

    =ARRAYFORMULA(vlookup(CEILING(sequence(counta(B:B)*3)/3+1),{row(B:B),B:B},2))

    assuming you wanted to generate three rows for each non-blank row in column B not counting the first one.

    Or if you want to be different, use a concatenate/split approach:

    =ArrayFormula(flatten(split(rept(filter(B:B,B:B<>"",row(B:B)>1)&"|",3),"|")))

    (all the above assume you want to ignore the first row in col B and start with 22).