Quote:
Originally Posted by peterbn74
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.