how to split data from 1 row into two rows continuously

[Guest]

trying to split data from 1 row into two separate rows, then copy and paste
the function(s) without skipping rows. i don't know how to word it better,
but here is my problem:

a b c d e f g h i j k
1 xyz 200 5.8 xyz 200 5.2 xyz 200 5.8
2 abc 400 10.6 abc 400 10.1 xyz 200 5.2
3 lmn 300 4.5 lmn 300 4.8 abc 400 10.6
4 qrs 500 8.7 qrs 500 9.5 abc 400 10.1
....

i want to copy data from row "1" from columns "a:c" into row "1" columns
"i:k" and then copy data from row "1" from columns "e:g" into row "2" columns
"i:k"...

finally, i would like to copy and paste the "i:k" columns downwards.

when i tried to do this with copy and paste, it would skip the data from
every other row.

sorry, i can't be more clear. hope someone can help. thanks in advance.

 

[Guest]

Take a look at Text to columns... under the Data menu.

 

[Roger Govier]

Hi

Enter the following formula in I1
=OFFSET($A$1,((ROW()-ROW($A$1))/2),COLUMN()-9)
Copy across through J1:K1

Enter in I2
=OFFSET($E$1,((ROW()-ROW($E$2))/2),COLUMN()-9)
copy across through J2:K2

Mark the block of cells I1:K2 and use the fill handle at the bottom right of
K2 to drag the formulae down the columns until you see Zeros appearing.

Regards

Roger Govier

 

[Guest]

This has already been answered, but I'll throw my solution out there just for
the heck of it:
=OFFSET(A$1,INT(MOD(ROW()-ROW($A$1),8)/2),MOD(ROW()+1,2)*3)
Note: The 8 was used because there was 4 rows of test data. It would
obviously need to be increased proportionately for more data.
Also, whether or not you add 1 would depend on what row you started on. I
wanted to alternate between 0 and 3 and I started on row 1.