# Custom transposing of a column into rows using VBA recursion

#### PC_Meister

##### Board Regular
Hello,

I have been scratching my head over this for a while but i can't seem to find the answer maybe because I have not done any recursive functions in a very long time . I have the following columns.

 Begin Shape1 End Shape2 Begin Shape1 End Shape3 Begin Shape2 End Shape4 Begin Shape4 End Shape6 Begin Shape3 End Shape5 Begin Shape5 End Shape7 Begin Shape5 End Shape8

<tbody>
</tbody>

I am trying to write a recursive subroutine that will generate the unique sequences showing the connectivity of the sequences, so for example for the case shown above we will get 3 sequences:

 Shape1 Shape2 Shape4 Shape6 Shape1 Shape3 Shape5 Shape7 Shape1 Shape3 Shape5 Shape8

<tbody>
</tbody>

A few observations I have noted:

• the number of unique sequences is equal to the number of shapes that occur only once in the column(Shape6, Shape7, Shape8 in this case). These will be the terminating shapes for a sequence
• Each sequence will always start by the shape in the first row (Shape1 in this case)

I was able to find a solution to this particular problem using nested loops but given that the length of the sequence of shapes is not constant, recursion appear as the most viable way. Any pointers will be great. Thanks!

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Is this the only list? You say there could be more... Could they form a loop - what would your result show if there is a loop in the sequence? (for example, if 1 to 2, 2 to 4, 4 to 6, 6 to 1)

I notice that only your top level (Shape 1) has multiple children.
What kind of output do you want if a child has multiple children, for example if Shape 3 had another child, Shape 77, in addition to the current child, Shape 5.

I would use a logic like

Given a parent/child pair (e.g. Shape3/Shape5)
Use Find to find all cells that have the parent in them. (If no such exist, then add the P/C to column 1)
Write the child in the cell to .Find.Offset(0,1)

repeat for all parent/children pairs

Last edited:
Is this the only list? You say there could be more... Could they form a loop - what would your result show if there is a loop in the sequence? (for example, if 1 to 2, 2 to 4, 4 to 6, 6 to 1)

No a loop (closed circuit) is not possible, all the sequence are linear.

I notice that only your top level (Shape 1) has multiple children.
What kind of output do you want if a child has multiple children, for example if Shape 3 had another child, Shape 77, in addition to the current child, Shape 5.

I would use a logic like

Given a parent/child pair (e.g. Shape3/Shape5)
Use Find to find all cells that have the parent in them. (If no such exist, then add the P/C to column 1)
Write the child in the cell to .Find.Offset(0,1)

repeat for all parent/children pairs

We would just follow the linear sequence. So suppose Shape77 has two children Shape88 and Shape99. The sequences would be
Shape1, Shape3, Shape77, Shape88
Shape1, Shape3, Shape77, Shape99

Replies
7
Views
522
Replies
4
Views
386
Replies
4
Views
991
Replies
0
Views
469
Replies
5
Views
840

1,221,241
Messages
6,158,736
Members
451,513
Latest member
EbenAgya

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back