## 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.