I am looking for Excel based macro to be written that will take links identified in the attached xls (ie Site A - Site B) and generate a report which indicates how they are connected together.
This simple tool will also be automated to identify:
- total number of hops taken to get back to the main site
- link types (as per my definition)
There are a few more criteria, but do not suspect that this will be to complicated.
The attached Excel spreadsheet has 4 tabs:
1) The "Network Diagram" tab is for information purposes only and? is not involved in any calculations.?
The "Link List Input" and "Fiber ID Input" are the two tabs that the macro will require for the calculations.
2)The "Link List Input" identifies the site to site link list (ie. Site A to Site B)
3)? The "Fiber ID Input" identifies the main site in a given cluster where all the traffic from the cluster is going to.
4) The? "Link Output" has three columns that require calculations. The "Link Output" should basically recognize the connectivity as shown in the "Network Diagram" based on the "Link List Input". The Fiber PoP ID column is determined from its association with all the links, i.e. all sites are required to hop across the network? back into a single Fiber PoP. The Link Type will either be a ring link or a spur link. The spur link will be further classified with "X - Y Spur" where X is the number of links away from the ring node and Y is the total number of spur links on that specific chain. The "Sites Traversing the Link" column counts the number of sites that will traverse that specific link back to the Fiber PoP based on a the minimum # of hops.
The "Site Output" tab generates a summary based on sites. The "Site Classification" will be either
3)Hub (intermediate site on a daisy chain)
"Min # of hops" column will generate the least number of hops from the site back to its respective Fiber PoP. "Max # of hops" column will generate the max number of hops from the site back to its respective Fiber PoP. Columns F and G will output the actual path as shown in attachment. The "# of remote sites homing" will output the number of sites that is passing through the site in question (including the site).
This tool should be flexible to include any number of links and fiber PoPs.
* * *This broadcast message was sent to all bidders on Monday Jun 30, 2008 9:42:54 AM:
Site A Site B A1 B B C D E F G G A1 C 1 C 2 B 3 B 4 The following links exist in the xls data. Site A is in Column A and Site B is in Column B. If you draw these connections on a piece of paper, you would in turn get the results in what you see in the "Network Diagram" worksheet. This information will be used to extract the results I am looking for. In this example the main site A1 has 13 sites feeding off of it. Make sense?