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

- is there a excel formula which will give me the highest year from column F, corresponding to every company code in column c
- Parse Full Name Into Parts
- Transfer data to other sheets/ranges based on dropdown menu on home page
- Excel formula to check a text value from a cell against table's first column and return a value from the table second column
- Excel Multiple Ranges - need different answers for each range (step function)
- VBA Workbooks stop working with upgrade to Excel 365 - VBA Compiling Issue?
- How can I search a range of multiple rows and columns for a value and return it's header?
- If File = "False" Application.GetOpenFileName Error 13 Type Mismatch
- Calculating percent error of each element of a list given a list of ratios
- SWITCH Function with multiple hits - Power Query
- Recursive Lambda to join full parent-child hierarchy
- Excel Conditional Formatting with PowerShell
- How to sort an Excel array in place
- No Such Interface supported while copying worksheets from source Workbook to destination Workbook - Excel VSTO Addin
- Excel formula for complex 2d table sum
- Export Word document as PDF using Excel VBA
- Lookup multiple criteria from column 2 in a table and return the value in column 1 if all match
- Why is the page header not saved when I save and close a workbook?
- ADODB recordset recordcount always returns -1
- Sum cell values below until a blank cell with Python
- VBA empty rows getting filled after running code
- Laravel Excel upload and progressbar
- FileSystem.FileCopy vs objFSO.CopyFile
- Excel VBA: Private Sub procedure too large
- VBA Checkbox combination enables not right?
- Excel macro works on one file, but not for multiple files when looping through a folder
- ASP.NET MVC FileResult is corrupting files
- Get specific json value
- Worksheet Function Max with condition
- Why is my auto fill not working?