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?

It is impossible to guess from your description and the test data what your right answer might look like. At first glance you seem to have dragged your formulas too far over the sheet. Please don't post pictures of textual data. The example data shown is supremely uninformative and should be provided as a CSV file so that others can examine it. It helps to have unique tags in every cell (eg =ROW()+COLUMN()*100 when trying to debug indexing code that is misbehaving. That way you will know where the value has come from. — Martin Brown
– Martin Brown, Commented May 22 at 22:12
In my example there are 5 headers that repeat: GNI, GNI PPP, SCHOOL, TD, GDP. I am attempting to pull all data with the GNI headers into a single column (stack the data). Likewise, I want all data with the GNI PPP headers to be "stacked" under a single column that I will call GNI PPP. Same intent applies for the remaining headers. In my example, 3 GNI headers become 1 GNI header, etc... — Code_Z
– Code_Z, Commented May 22 at 22:19

VBasic2008 · Accepted Answer · 2025-05-23 01:18:59Z

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,
```

edited May 23 at 1:18

answered May 22 at 23:28

VBasic2008

56.9k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Code_Z May 23 at 13:52

Spot on! This is exactly what I was trying to figure out. Thank you so much! Very much appreciated, it will go into my library of knowledge.

Code_Z May 23 at 15:44

I've gone a step further with the MS365 functions and added a filter function to stack a specific column.

=FILTER(LET(data,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),{0,0,1,0,0})

It stacks only the desired column. I'm curious if blank cells in can be ignored and not included in the stacked column, to eliminate multiple "" or "0" values returned.

Michal · Accepted Answer · 2025-05-22 22:33:44Z

0

If I understand your question correctly, you want to sum together all columns with the same given header, if this is so, you could use:

=BYROW(A2:F3, LAMBDA(row, SUM(FILTER(row, (A$1:F$1="a")))))

For earlier Excel versions:

=SUMPRODUCT((A$1:F$1=$G$1)*(A2:F2))

and drag it down to cover all rows.

answered May 22 at 22:33

Michal

7,6894 gold badges30 silver badges47 bronze badges

1 Comment

Code_Z May 23 at 16:28

No, I wasn't trying to Sum them, although what you shared is valuable and I will hang on to that knowledge as well. I was trying to simply stack them like a VSTACK would do, but I'm dealing with over 3000 columns so VSTACK would be too cumbersome to write. I am trying to now figure out how to get the filter function to work with the MS365 functions to remove blank values when I'm selecting a specific column to be stacked, such as: Filter(Filter(range,range<>""),{0,0,1,0,0})

Collectives™ on Stack Overflow

Excel Cyclical Offset

2 Answers 2

Stack Repeating Columns

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Stack Repeating Columns

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related