Excel Cyclical Offset

Question

I'm attempting to pull in multiples columns (with specific ranges) into a single column. I've been running in circles (pardon the pun) to no avail. Here is my formula thus far on sheet3!A2:

=OFFSET(Sheet2!A2:O26,MOD(ROW()-2,COUNTA(Sheet2!A:O)-1),ROUNDDOWN((ROW()-2)/(COUNTA(Sheet2!A:O)-1),0)*5)

sheet2 contains the data:

I'm stuck here. I can't get the data to stack under the columns. Instead, it continues to spill out into the columns to the right of my target columns. There should only be 5 column with these headers: GNI, GNI PPP, SCHOOL, TD, and GDP. What did I mess up?

Answer 1

Stack Repeating Columns

• The requirement is a formula that will produce (in OP's data) the same result as the formula =VSTACK(A2:E26,F2:J26,K2:O26) possibly with functions available in versions 2019 and/or earlier.

Legacy

=INDEX(Sheet2!$A$2:$O$26,
    MOD(ROW($A2)-ROW($A$2),ROWS($A$2:$A$26))+1,
    MOD(COLUMN(A$2)-COLUMN($A$2),COLUMNS($A$2:$E$2))+1+
        INT((ROW($A2)-ROW($A$2))/ROWS($A$2:$A$26))*COLUMNS($A$2:$E$2))

or

=INDEX(Sheet2!$A$2:$O$26,
    MOD(ROW($A1)-ROW($A$1),25)+1,
    MOD(COLUMN(A$1)-COLUMN($A$1),5)+1+
        INT((ROW($A1)-ROW($A$1))/25)*5)

MS365

=LET(data,Sheet2!A2:O26,cols,5,
    rc,ROWS(data),
    ss,COLUMNS(data)/cols,
    ri,WRAPROWS(TOCOL(IF(SEQUENCE(,cols),TOCOL(IF(SEQUENCE(,ss),SEQUENCE(rc)),,1))),cols),
    ci,WRAPROWS(TOCOL(TOCOL(IF(SEQUENCE(rc),SEQUENCE(,cols)))-1+SEQUENCE(,ss,,cols),,1),cols),
    r,INDEX(data,ri,ci),
    r)

• Replace the bottom-most r with any other variable to see what it holds.

Simpler Data

Legacy

=INDEX($A$2:$F$5,
    MOD(ROW($A2)-ROW($A$2),ROWS($A$2:$A$5))+1,
    MOD(COLUMN(A$2)-COLUMN($A$2),COLUMNS($A$2:$C$2))+1+
        INT((ROW($A2)-ROW($A$2))/ROWS($A$2:$A$5))*COLUMNS($A$2:$C$2))

or

=INDEX($A$2:$F$5,
    MOD(ROW($A1)-ROW($A$1),4)+1,
    MOD(COLUMN(A$1)-COLUMN($A$1),3)+1+
        INT((ROW($A1)-ROW($A$1))/4)*3)

MS365

• Using the above formula, the first line looks like this:

  =LET(data,A2:F5,cols,3,