ExcelBanter - column to row draging

ExcelBanter (https://www.excelbanter.com/)

- New Users to Excel (https://www.excelbanter.com/new-users-excel/)

- - column to row draging (https://www.excelbanter.com/new-users-excel/447586-column-row-draging.html)

column to row draging

I have a column of data (say) in Column A. Then I need to copy each 5 data into a row. For example, A1, A2..., A5 into D1, E1, F1, G1, H1; A6, A7,..., A10 into D2, E2, F2, G2, H2; A11, A12,..., A15 into D3, E3, F3, G3, H3.....
.......
My idea is to type A1 into D1, the using drag for E1=A2, F1=A3, G1=A4, H1=a5. then type A6 into D2, the using drag for E2=A7, F2=A8, .... , H2=A10. and so on.

Unfortunately, once I drag D1 to the right, I get E1=B1, F1=C1, G1=D1, H1=E1. Putting $D1 does not work neither. Any help? Thanks.

Quote:

Originally Posted by peterbn74 (Post 1607137)

I have a column of data (say) in Column A. Then I need to copy each 5 data into a row. For example, A1, A2..., A5 into D1, E1, F1, G1, H1; A6, A7,..., A10 into D2, E2, F2, G2, H2; A11, A12,..., A15 into D3, E3, F3, G3, H3.....
My idea is to type A1 into D1, the using drag for E1=A2, F1=A3, G1=A4, H1=a5. then type A6 into D2, the using drag for E2=A7, F2=A8, .... , H2=A10. and so on.
Unfortunately, once I drag D1 to the right, I get E1=B1, F1=C1, G1=D1, H1=E1. Putting $D1 does not work neither. Any help? Thanks.

Dear Peterbn74, Good afternoon.

Try to do this:

D1 -- =INDIRECT((ADDRESS((ROW())+((ROW()-1)*4),1,3,1,)))
E1 -- =INDIRECT((ADDRESS((ROW())+((ROW()-1)*4)+1,1,3,1,)))
F1 -- =INDIRECT((ADDRESS((ROW())+((ROW()-1)*4)+2,1,3,1,)))
G1 -- =INDIRECT((ADDRESS((ROW())+((ROW()-1)*4)+3,1,3,1,)))
H1 -- =INDIRECT((ADDRESS((ROW())+((ROW()-1)*4)+4,1,3,1,)))

Now you can push down the formulas.

Please, try this one and tell me if it worked for you.