Reformatting excel file?

Question

I need to reformat a file like below from:

a b c 1 2 3

d e f 4 5 6

g h i 7 8 9

To:

a b c

1 2 3

d e f

4 5 6

g h i

7 8 9

TIA!

Answer 1

Assume your original data is in A1 to F3,

1. insert a column before column D then insert a column before column A,

2. put 1 in A1, =A1+2 in A2 copy A2 down to A3. Put 2 in E1, =E1+2 in E2 copy E2 down to E3.

3. copy A1:A3, right click A1, paste special, values, copy E1:E3, right click E1, paste special, values

4. move E1:H3 to A4, select A1:D6, sort on column A

Answer 2

Assuming your data is in six columns, A1:F1000:

This formula will take a row of 6 cells and display the data as two rows of 3 cells.  Replace all instances of $A$1 and A1 with the address of the first cell of your data, and $A$1:$F$1000 with the range of your whole table.  Take care to use the $ signs as indicated.

=INDEX($A$1:$F$1000, INT((ROWS($A$1:A1)+1)/2), COLUMNS($A$1:A1) + MOD(ROWS($A$1:A1)+1, 2) * 3)

Put the formula in an empty cell (say, in G1), and then copy it over (i.e. to I1) and down as far as it needs to go.

If the results are satisfactory, remove the formulas, leaving only the results by copying the data, right-click > paste special > choose "values".