How to transpose and move data

Hello,

Have a data set which is 2 columns and 596 rows, in which I need to transpose parts of the data set, remove empty columns, and then line up the data in 6 columns, 40 rows.

Example starting data set (with 4 empty rows between each row of data):

1 1




2 2




3 3




4 4




5 5




6 6




7 7




8 8




9 9


Example ending data set (0 empty rows):

1 1 2 2 3 3
4 4 5 5 6 6
7 7 8 8 9 9

Can someone assist with some macro code to accomplish this task?

Thank you!