• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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
please help

thanks in advance

Regards
SUDARSAN
 

Attachments

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

thanks in advance
 
Top