Tree Network

**aginn565** · 05-20-2022, 03:34 AM

Hi,
I design gas networks and I am looking into incorporating our design calculator (an old DOS program) into Excel but it is beyond my skills.

I have attached a spreadsheet that shows a 'tree' network that I draw in AutoCAD. I then have a routine that exports all of the pipes from AutoCAD to Excel (columns A-C in the attached spreadsheet).

I am trying to extract every possible route from the first number (1) to each extremity. The possible scenarios are shown in columns E-G, I-K, M-O & Q-S. This is a small example and could end up being hundreds of pipes.

I would really appreciate if someone could help with this or at least tell me if it is possible in Excel!

Many thanks,

Andy

**AliGW** · 05-20-2022, 03:41 AM

Administrative Note:

Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

**menem** · 05-20-2022, 05:18 AM

If you don't mind to use a lot of helper cells.

1. Count how many possible route
E5
=COUNTIF($A$5:$A$10,B5)
F4
=COUNTIF($E$5:$E$10,0)

2. Create route from the last to the first.
H5
=IF($G5<=$F$4,INDEX($B$5:$B$10,AGGREGATE(15,6,(ROW($E$5:$E$10)-ROW($E$4))/($E$5:$E$10=0),$G5)),"")
I5 (copy to right as much of your longest route noeds)
=IF($G5<=$F$4,IFERROR(INDEX($A$5:$A$10,MATCH(H5,$B$5:$B$10,0)),""),"")

3. Summary route
H16 (first element of each route)
=IFERROR(AGGREGATE(15,6,OFFSET($G$4,$F16,1,1,COLUMNS($H$4:$Q$4)),$G16),"")
the others formula is just sumifs

Note: In the result , it will repeated last node without connected node and length.

Regards.

**aginn565** · 05-20-2022, 09:15 AM

Hi,

Thank you for this that looks amazing! I will have a go at incorporating into my spreadsheet and let you know if I have any issues.

One question I have is regarding the following formula: =IF($G5<=$F$4,INDEX($B$5:$B$100,AGGREGATE(15,7,(ROW($E$5:$E$100)-ROW($E$4))/($E$5:$E$100=0),$G5)),"")

What does the 15,7 refer to?

**menem** · 05-20-2022, 10:24 PM

It's not 15,7 , it's 15,6

15 is refer to function number 15 (small value)
6 is options , 6 = ignore error values.

Regards.

**aginn565** · 05-25-2022, 05:05 AM

Hi,

I have incorporated your spreadsheet into mine and it works great, thanks!
There is one issue that I have come across and that is we can sometimes have backwards nodes (see example). Can you think of a way around this? As you will see the data on sheet 1 always lists the highest node number in the second column. If this is getting too complex then please let me know!

**AliGW** · 05-25-2022, 05:38 AM

Are you still using Excel 2010?

**aginn565** · 05-25-2022, 05:47 AM

Hi, yes I am.

**menem** · 05-25-2022, 06:01 AM

I can't find out the formula that can be use in every cells.
I need to modify each formula in each column.

like....
in the column I (the 1st of next_previous value) formula will be.
=IF($G5<=$F$4,IFERROR(INDEX($A$5:$A$11,AGGREGATE(15,6,(ROW($B$5:$B$11)-ROW($B$4))/(1/($B$5:$B$11=H5)),COUNTIFS($H$4:$H5,$H5))),""),"")

then in column J (the 2nd of next_previous value).
=IF($G5<=$F$4,IFERROR(INDEX($A$5:$A$11,AGGREGATE(15,6,(ROW($B$5:$B$11)-ROW($B$4))/(1/($B$5:$B$11=I5)),COUNTIFS($H$4:$H5,$H5,$I$4:$I5,$I5))),""),"")

the next will be (K)
=IF($G5<=$F$4,IFERROR(INDEX($A$5:$A$11,AGGREGATE(15,6,(ROW($B$5:$B$11)-ROW($B$4))/(1/($B$5:$B$11=J5)),COUNTIFS($H$4:$H5,$H5,$I$4:$I5,$I5,$J$4:$J5,$J5))),""),"")

Regards.

**aginn565** · 05-25-2022, 06:50 AM

Hi, thank you for your help, I really appreciate it!
Unfortunately it doesn't appear as if all of the routes are working on the new example and I think it is the backwards node 10 that is the reason.
I have a attached a new spreadsheet that shows the required routes on Sheet 1 (columns E-S).
Is this possible?

**menem** · 05-25-2022, 10:11 PM

Please review in sheet3 (I've copy above formula into sheet3).
And may I ask, should 5-10, 6-10, 7-10 should be separated from the previouse route ?

Capture.JPG

Regards.

**aginn565** · 05-26-2022, 03:24 AM

The routine needs to show every possible route from node 1 (the 1st node) to each extremity so nodes 5-10, 6-10 & 7-10 don't need to be separated.
The routes I need to show on the image from sheet 1 would be:
Node 1-2-3
Node 1-2-10-5
Node 1-2-10-6
Node 1-2-10-7

Your original formula works great for nodes that run in sequence but it doesn't seem to like it when the nodes don't run in numerical order.

If this is too complex I can just try to ensure that the nodes always run in sequence but I wanted to try and cover every scenario.

I hope this makes sense!

**menem** · 05-26-2022, 04:01 AM

Edited, swap manually can't solved problem.

In that case, could the data swap as in a picture ?

Capture.JPG

If yes, the old formula will be worked.

Regards.

**menem** · 05-26-2022, 05:45 AM

If you can't swap it manually.
You may use swapper area ( as in Sheet2 (2) )

NodeFrom
=IF(F5=B5,A5,B5)

NodeTo
=IF(A5<>1,IF(COUNTIF($A$5:$B$10,A5)=1,A5,B5),B5)

And in result area will be need to change formula to
L16

Please Login or Register  to view this content.

Regards.

**aginn565** · 05-26-2022, 06:08 AM

Thank you so much for your help. I will have a play around with the new formula and see how I get on!

Tree Network

LinkBack

Thread Tools

Rate This Thread

Display

Tree Network

Re: Tree Network

Re: Tree Network

Re: Tree Network

Re: Tree Network

Re: Tree Network

Re: Tree Network

Re: Tree Network

Re: Tree Network

Re: Tree Network

Re: Tree Network

Re: Tree Network

Re: Tree Network

Re: Tree Network

Re: Tree Network

Thread Information

Users Browsing this Thread

Similar Threads

Dependency Tree

[SOLVED] Vba to copy a file from a network path and place it to another network path location

[SOLVED] Transforming 1level coded product tree to few level product tree

Decision Tree

topology network tree

Tree Structure

[SOLVED] VB Tree View

Bookmarks

Bookmarks

Posting Permissions