Search code examples
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:

    enter image description here

    • Formula used in cell C1

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

    Or,

    enter image description here

    • Formula used in cell C1

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

    For older versions :

    enter image description here

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

    enter image description here

    • Formula used in cell C1

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

    Or,

    enter image description here

    • Formula used in cell C1

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