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 a single column.

Excel’s OFFSET formula to the rescue, 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 of repeating a value 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.