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.