I would like to make a list of all successors when only given a list with immediate successors and predecessors.

[TBeelman]

Hello All, I am tasked with a project where I need to solve the impact of constraints. To do this I need to figure out all the tasks that are successors to the tasks that are constrained. I have a list of all the tasks along with their immediate successors and immediate predecessors. I would like to be able to form a list of off successive tasks until the completion. In My example I would like for a shortage in E to tell me that all the successors are D, H, I, J, and K. If I have a shortage in task B I would like to know that tasks F, G, and K are constrained as well. My list has over 3000 operations but they are broken up into 10 groups so around 300 operations per group.(to give you an idea of the size). I need them in a format that I will be able to delete duplicates so I am not counting the same operation twice when I have multiple constraints. Any and all feedback is greatly appreciated. Thanks.

Operation	Successor	Predecessor
A	B
A	C
A	D
B	F	A
B	G	A
E	D
E	H
D	I	A
D	J	E
I	K	D
J	K	D
F	K	B
G	K	B

<tbody>
</tbody>

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

You need a recursive type of search. That is, a search that will find the first Successor and then you run the same search again using the Successor as the starting point.

I have assumed that the Operation and Successor are in columns A and B and that the data starts in row 2. I have also assumed that the row in Column A after the data will contain a null character ("").

Private List As Collection

Function FindSuccessor(ByVal Operation As String)
    If Operation <> "" Then
        On Error Resume Next
        List.Add Operation, CStr(Operation)
        On Error GoTo 0
        i = 2
        Do
            If Cells(i, 1).Value = Operation Then FindSuccessor (Cells(i, 2).Value)
            i = i + 1
        Loop Until Cells(i, 1).Value = ""
    End If
End Function

Sub xx()
    Set List = New Collection
    FindSuccessor ("A")
    For i = 1 To List.Count
        Debug.Print List.Item(i)
    Next
End Sub

As new Successors are found they are added to a Collection. The Collection is keyed so that duplicates are not stored. The "On Error" processing stops errors appearing when duplicates do occur.

Sub xx is the starting point. The above program is starting from "A". It calls FindSuccessor which looks for "A" in column A and finds its Successor in Column B. It then passes "B" to itself and does the same with "B" etc. Each Successor found is stored in the Collection called List.

The loop in xx displays the results.