# How put a function/formula to select row A1:H1 and transpose to column A1:A8 then select row A9:H9 and transpose A9:A18 like so

#### SUDARSAN

##### Member
Hi Friends,
need a help to complete my task
i need to transpose the data of A1: H86000 rows in to L1:L...
but it should not select next row and transpose ,
it should select every 8th row and transpose
how to do this

Regards
SUDARSAN

#### Attachments

• 19.8 KB Views: 8

#### Excel Wizard

##### Active Member
Please try at L2
=INDEX(A:H,INT((ROWS(L\$2:L2)-1)/8)*8+1,MOD(ROWS(L\$2:L2)-1,8)+1)

#### Attachments

• 21.5 KB Views: 7

#### SUDARSAN

##### Member
Please try at L2
=INDEX(A:H,INT((ROWS(L\$2:L2)-1)/8)*8+1,MOD(ROWS(L\$2:L2)-1,8)+1)
Thanks a lot ,
i can not believe it

thank you once again you helped me lot

#### Peter Bartholomew

##### Well-Known Member
It is easy enough to create a column of values using a formula
=INDEX(source,1+FLOOR(k,8),1+MOD(k,8))
where k are the natural numbers starting at 0.
Something a formula will not do is place the sequence in column A since that implies overwriting existing values.
For that you would need to carry it out as a manual process (copy/paste-transpose) or use VBA.

#### p45cal

##### Well-Known Member
Office 365?
In any single cell:
Code:
``=INDEX(A:H,ROUNDDOWN(SEQUENCE(CEILING(362,8),1,0)/8,0)*8+1,MOD(SEQUENCE(CEILING(362,8),1,0),8)+1)``
or the same but a bit more general:
Code:
``=INDEX(A1:H362,ROUNDDOWN(SEQUENCE(CEILING(ROWS(A1:H362),8),1,0)/8,0)*8+1,MOD(SEQUENCE(CEILING(ROWS(A1:H362),8),1,0),8)+1)``

#### SUDARSAN

##### Member
It is easy enough to create a column of values using a formula
=INDEX(source,1+FLOOR(k,8),1+MOD(k,8))
where k are the natural numbers starting at 0.
Something a formula will not do is place the sequence in column A since that implies overwriting existing values.
For that you would need to carry it out as a manual process (copy/paste-transpose) or use VBA.
Thank you

#### SUDARSAN

##### Member
Office 365?
In any single cell:
Code:
``=INDEX(A:H,ROUNDDOWN(SEQUENCE(CEILING(362,8),1,0)/8,0)*8+1,MOD(SEQUENCE(CEILING(362,8),1,0),8)+1)``
or the same but a bit more general:
Code:
``=INDEX(A1:H362,ROUNDDOWN(SEQUENCE(CEILING(ROWS(A1:H362),8),1,0)/8,0)*8+1,MOD(SEQUENCE(CEILING(ROWS(A1:H362),8),1,0),8)+1)``

Thank you

#### SUDARSAN

##### Member
Office 365?
In any single cell:
Code:
``=INDEX(A:H,ROUNDDOWN(SEQUENCE(CEILING(362,8),1,0)/8,0)*8+1,MOD(SEQUENCE(CEILING(362,8),1,0),8)+1)``
or the same but a bit more general:
Code:
``=INDEX(A1:H362,ROUNDDOWN(SEQUENCE(CEILING(ROWS(A1:H362),8),1,0)/8,0)*8+1,MOD(SEQUENCE(CEILING(ROWS(A1:H362),8),1,0),8)+1)``
Thank you Thanks a lot
but I am using standard 2016 and office 365 on Mac OS

the sequence function may not work. any alternative please