## Transpose a Range to a Single Column

Excel has the ability to transpose data from vertical to horizontal or in reverse using formula. I was recently asked by a colleague to create an Excel formula which would transpose a range of cells into a single column. The range of the cells was to be fixed and the output was to appear sequentially down one column. This is a tricky task so it took some time to crack. The following is an illustration of the task to be achieved with the result in Blue.

I came up with this offset formula, I am sure there are other ways but this seems to achieve the task in a smallish amount of characters.

=OFFSET($A$11,CEILING(ROW(A1)/COLUMNS(A:C),1)-1,MOD(ROW(A1)-1+COLUMNS(A:C),3))

After the task was complete my colleague asked if each number could be repeated three times in a single column. So for example (22,22,22,33,33,33 etc The Excel formula to achieve that task is as follows;

=OFFSET($A$11,CEILING(ROW(H1)/($H$7*3),1)-1,MOD(CEILING(ROW(A1)/$H$7,1)-1,COLUMNS(A:C)))

Where H7 contains the number 3.

Breaking the Excel formula down we are starting the Offset in A11 which is 22. We want to repeat that value 3 times. Offset start=A11, now we want to offset 0 rows and 0 columns times 3. So we need 0 to appear as the Rows to offset and we need this to appear 9 times. (22,22,22,33,33,33,44,44,44). The following formula will do that:

=CEILING(ROW(H1)/(3*3),1)-1

Now we want to produce a number sequence which looks like 0,0,0,1,1,1,2,2,2 and keep repeating this sequence. The following Excel formula will create this sequence.

=MOD(CEILING(ROW(A1)/$H$7,1)-1,3)

The first formula will say;

=Offset(A11,0,0)

and will produce the result 22, then the formula will kick in and start producing the required sequence. The following Excel file shows the above example. I have put a variable cell H7 where you can tell the formula how many times to repeat the sequence.