Custom transposing of a column into rows using VBA recursion

[PC_Meister]

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.

[TABLE="class: grid, width: 250, align: center"]
<tbody>[TR]
[TD]Begin[/TD]
[TD]Shape1[/TD]
[/TR]
[TR]
[TD]End[/TD]
[TD]Shape2[/TD]
[/TR]
[TR]
[TD]Begin[/TD]
[TD]Shape1[/TD]
[/TR]
[TR]
[TD]End[/TD]
[TD]Shape3[/TD]
[/TR]
[TR]
[TD]Begin[/TD]
[TD]Shape2[/TD]
[/TR]
[TR]
[TD]End[/TD]
[TD]Shape4[/TD]
[/TR]
[TR]
[TD]Begin[/TD]
[TD]Shape4[/TD]
[/TR]
[TR]
[TD]End[/TD]
[TD]Shape6[/TD]
[/TR]
[TR]
[TD]Begin[/TD]
[TD]Shape3[/TD]
[/TR]
[TR]
[TD]End[/TD]
[TD]Shape5[/TD]
[/TR]
[TR]
[TD]Begin[/TD]
[TD]Shape5[/TD]
[/TR]
[TR]
[TD]End[/TD]
[TD]Shape7[/TD]
[/TR]
[TR]
[TD]Begin[/TD]
[TD]Shape5[/TD]
[/TR]
[TR]
[TD]End[/TD]
[TD]Shape8[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Shape1[/TD]
[TD]Shape2[/TD]
[TD]Shape4[/TD]
[TD]Shape6[/TD]
[/TR]
[TR]
[TD]Shape1[/TD]
[TD]Shape3[/TD]
[TD]Shape5[/TD]
[TD]Shape7[/TD]
[/TR]
[TR]
[TD]Shape1[/TD]
[TD]Shape3[/TD]
[TD]Shape5[/TD]
[TD]Shape8[/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[s hal]

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)

 

[mikerickson]

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

 

[PC_Meister]

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

Not entirely sure I follow your logic about the parent/child pair. Can you please elaborate a little more? Thanks