CODEWITHSUNDEEP
google-sheetsarray-formulas
Felix Dombek
Felix Dombek

Reputation: 14382

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.

Upvotes: 0

Views: 193

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34420

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

Upvotes: 2

Related Questions