How do I transponse a pair of excel columns in such a manner that the data appears side by side and not up and down?

Question

This is what I want to do, is there a simple way for me to do the same?

Answer 1

Here are two solutions:

Option 1: Without OFFSET

Due to the volatile character of OFFSET, I revised my previous answer (option 2) which does no longer use OFFSET. More complex, but does consume lesser resources.

D4 features =CHOOSE(MOD(COLUMN(),2)+1,INDEX($A$3:$A$5,ROUNDUP((COLUMN()-COLUMN($C$4))/2,0),MOD(COLUMN(),2)),INDEX($B$3:$B$5,ROUNDUP((COLUMN()-COLUMN($C$4))/2,0),MOD(COLUMN();2)))

• CHOOSE determines, whether the result should derive from column A or B (see the different anchors in both INDEX formulas. Apart from that, both formulas are equal.
• The INDEX formula is explained in Option 2.

Option 2: With OFFSET

Here is a solution with the OFFSET function, driven by COLUMN.

D4 features the following formula

=OFFSET($A$2,ROUNDUP((COLUMN()-COLUMN($C$4))/2,0),MOD(COLUMN(),2))

Breakdown

• OFFSET(anchor, rows, columns)
• anchor: first cell of the headline row
• rows: takes column of current cell and subtracts the column of the labeled cell (here "Output:") to get a sequence of ongoing numbers. As you are interested in the result of two adjacent cells, I divide the result by two. To get an integer value, I round the result up. This way, the source row is incremented by every two columns.
• columns: takes the column of the current cell divides it by two and uses the result to determine whether we take the content of the first or second column.