Being back to this thread by accident I want to attach a much simpler and more efficient (IMO) solution:
(End of edit)
See attached example.
Of course, you may also calculate the elements inside the range of transposed sections of the original column one by one using OFFSET (or INDEX) and thereby avoid the well known disadvantages of array formulae concerning maintenance/enhancements/scaling.
(I cannot imagine a somehow reaonable case of a data collection needing such a rearrangement. I would not expect much use of it, except, maybe, for printing.)
I didn’t check the quoted formula.
Meanwhile I had a look on the quoted formula. It simply is breaking and transposing the ‘Source’ in pieces assuming there are no blank cells and the length of each section is 6. The Source itself is given by a fix RangeAddress. This is covered by formulae for ‘Rearranging Rectangular Ranges’ which are to find here and there. This obviously does not match your much more complicated needs. Didn’t you see that?
The solution I proposed is adapted to your needs introducing some helper columns in a way I prefer because it allows for clearly structured solutions. However, it requires a bit of additional adaptions if the size of the job got scaled up.
Now we are in a dilemma:
Either you acquire a basic understanding of the solution and then are capable of doing the filling down of formulae as needed for your actual task. Of course, you also may find a solution better adapted to your needs, better structured, whatever… based on your understanding.
Or you try to get a “fool-proof” solution by someone doing a professional job.
If you get your solution the second way, it will be much more complicated and much less efficient because of the requirements of being fool-proof. No chance that a (kind of) beginner will understand it on a level to be able to maintain and to enhance it himself. And: One day you may urgently want a variant or an additional feature. Who should enhance the solution then? Do you want to depend on a specialist’s support in the long run? I won’t be that specialist.
Thus I want to emphatically advise you to choose the first way. It are users on the way of understand-and-do-it-yourself-then this site is made for.
Edit regarding additional comments.
Simply use the file I attached as an engine:
- Fill in your 1-column-data into column A of sheet ‘Solution’ beginning with A2.
- Fill down the formulae in the helper columns as far as data are present in column A.
- Fill down the array formulae in the H through P to get as many output rows as sections were found (I expect a maximum of about 3500 rows). Best you do that using a menu path to ‘Fill’ > ‘Down’. If you want to do it dragging the fill handle, you have to press and keep pressed the Ctrl-key in addition.
You may ‘Copy’/‘Paste Special…’ (values only) elswhere, of course.