Transpose a pair of columns into a single row

Question

I have a two column, n row table in this format:

1 | Apple
2 | Orange
3 | Fruit
4 | Car

I need to reformat all of the data into one row like so:

1 | Apple | 2 | Orange | 3 | Fruit | 4 |Car

I am aware of Paste Special using Transpose, but that still gives me a row for each column.

What would be the best approach to this?

Answer 1

If 1 is in A2, the in C2:

=OFFSET($A2,COLUMN()/2,0)  

and in D2:

=OFFSET($B2,COLUMN()/2-1,0)  

then drag the formulae across as a pair.

OFFSET
COLUMN