How to use ARRAYFORMULA with OFFSET?

Question

Any thoughts why doesn't this work? I need the J column to be auto-populated. It works correctly if I just stretch down the formula. However I need it to dynamically adjust its size according to the number of elements in column G

Is that even achievable?

user_5632629 · Answer

try:

=QUERY(H6:H, "offset 1", 0)

or in array:

=ARRAYFORMULA({G6:G, QUERY(H6:H, "offset 1", 0)})

UPDATE:

F3 cell:

=FILTER(B2:B; MOD(ROW(A2:A)-2; 4)=0)

G3 cell:

=FILTER(B2:B; MOD(ROW(A2:A)-3; 4)=0)

H3 cell:

=FILTER(B2:B; MOD(ROW(A2:A)-1; 4)=0)

I3 cell:

=FILTER(C2:C; MOD(ROW(A2:A); 4)=0)

user_9593895 · Answer

I think that I may have something that helps you. The only weakness is that it currently uses two "helper" columns. With some on the formula, these could be removed. Here is my sample.

First, I generate more of a database structure for the data, adding the element name to each row of data, where you have blanks in column A. I also pull the "prop4" value from the second column. If your data structure for column B and C is different then what you've shown, this formula might need adjustment. This formula does that:

FILTER(ARRAYFORMULA({ 
    VLOOKUP(ROW(A2:A); 
      IF(A2:A<>""; 
        {ROW(A2:A)\ A2:A}); 
      2; 1) 
 \
    IF(LEN(B2:B);
      IF(MOD(ROW(B2:B);4)=0;
        C2:C;B2:B);
        ISERROR(1/0))
  });
  LEN(B2:B))

The result looks like column K and L in this:

Then I use a query based formula on this intermediate result, giving the final result table. The formula here (found in this question) is:

=ARRAYFORMULA(REGEXREPLACE(TRIM(SPLIT(TRANSPOSE(QUERY(QUERY(
 IF(L1:L<>""; {K1:K&"♠"\ L1:L&"~"}; );
 "select max(Col2) where Col1 is not null group by Col2 pivot Col1")
 ;;999^99)); "♠~";1;1)); "~$"; ))

I tested this by adding an element "block" at the end of column A, and by deleting an element block in the middle of column A, both of which which worked fine.

Let us know if that is the result you want. If not, please explain exactly what you expect, given your data.

How to use ARRAYFORMULA with OFFSET?

2 Answers

UPDATE: