Trace all possible paths & Duration as per the Directed Graph

[gemma-the-husky]

Where's the data?
I used the graph in #36

 

[arnelgp]

how many point did you make 139?
see my demo with 25 the result is instant.

 

[gemma-the-husky]

I didn't realise there was a second tab on the spreadsheet.
I am working on that now.

 

[gemma-the-husky]

Before I do that, if I take the spreadsheet in #36

Activity CIV-004 is listed 3 times
Activity CIV-007 is listed 6 times
and so on.

There are 95 unique activities, rather than 107. Is that correct?

and then
Source - Destination CIV002-CIV004 is listed 3 times
Source - Destination CIV004-CIV007 is listed 6 times

Am I misunderstanding something?

 

[arnelgp]

There are 95 unique activities, rather than 107. Is that correct?

that is the challenge you only need to work on the unique values.
also, you did not notice, 100Z has No Duration.

 

[gemma-the-husky]

@arnelgp

Activity 100Z isn't defined in the activities list in column I
Did you add it as an activity, to make 96 activities, or did you just ignore it. It doesn't appear to do anything.

 

[gemma-the-husky]

@arnelgp

Activities ARC-026 and CIV-026 are both defined twice in the list of activities, with different durations. How did you handle that?

 

[arnelgp]

i picked the first one.

 

[gemma-the-husky]

OK. This version does it all inside a database, rather than writing to excel.
I loaded in the activities, and paths to store in internal data structures.

It does the 216 routes from start to ARC-013 in .125 seconds
It does 14076 routes for the whole thing in about 3.5 minutes

I renumbered the duplicate activities, so the second ones are effectively orphans.
I haven't cross checked the output.

No sorting, just stores the routes in a text string, and outputs to a csv file when finished.
Bigger than I expected. The output csv for the full graph is nearly 4Gb.

Acer Nitro Laptop i5 processor with 8Gb of memory.

Interesting exercise!

 

[arnelgp]

well its a little slow, from B (47) to 200Z(2):

while my worksheet, From B to 200Z:

same Number of rows generated: 14,076.

 

[gemma-the-husky]

@arnelgp
I based the timing on the 6 minutes quoted by @Futurz in #36
Was that based on an earlier solution?

Anyway, I will have a look at your solution to see where the time savings are being achieved.
As I say, it was interesting, and it's good we agree on the number of paths!