excelexcel-formulatransposeoffset

# Excel function to transpose every nth row into columns

I have a sheet with a large row of data but the relevant data are every 3rd row

In Sheet1

Col A
First
Second
Third
Fourth
Fifth
Sixth
Seventh

In Sheet2, I would like to transpose the relevant rows to update automatically as column headers, i.e.,

First Fourth Seventh

I tried a variety of offset/transpose etc. functions, but I always seems to get it updating the column (so as I drag the formula it would change A1 to B1 and so forth, but I want it to do A1 to A4 to A7 etc.

Solution

• Try:

• Formula used in cell C1

``````=TOROW(FILTER(A1:A7,MOD(SEQUENCE(ROWS(A1:A7))-1,3)=0))
``````

Or,

• Formula used in cell C1

``````=TOROW(IFS(MOD(SEQUENCE(ROWS(A1:A7))-1,3)=0,A1:A7),3)
``````

For older versions :

• Formula used in cell C1

``````=INDEX(\$A\$1:\$A\$7,AGGREGATE(15,6,
(ROW(\$A\$1:\$A\$7)-ROW(\$A\$1)+1)/(MOD(ROW(\$A\$1:\$A\$7)-1,3)=0),
COLUMN(A1)))
``````

Note : Approaches shown above doesn't uses any volatile functions, since volatile functions(viz. OFFSET() & INDIRECT() ), it does not just recalculate anytime something on the sheet changes, it will recalculate anytime anything on any open excel workbook changes!

Another nice alternative suggested by P.b Sir.

• Formula used in cell C1

``````=LET(n,3,TOROW(TAKE(WRAPROWS(A1:A7,n),,1)))
``````

Or,

• Formula used in cell C1

``````=TAKE(WRAPCOLS(A1:A7,3),1)
``````