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.


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;


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:


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.


The first formula will say;


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.