Tree / Level Structure "From & To" Input

**Mike_S** · 03-06-2010, 02:06 AM

Havings read an earlier thread "Setting up Tree Structure", Forum Guru, JBeaucaire came up with a briliant solution which is exactly what I need but I require to allow for more levels. I have had only limited experience and am finding it difficult to follow methodology (seems to use autofilter) so as to adapt it.

Essentially I want to input input data with "from" & "to" nodes and generate a level structure. A spreadsheet is attached which shows input and output format. The borders and cell colours are not essential in output sheet but would like to end up with a similar tree structure which can be prettied up manually as shown.

The numer of input rows could be upto 250 with upto 9 levels.

If it is too difficult no problems.

Mike

**JBeaucaire** · 03-06-2010, 10:21 AM

Give this a try on the sample data, then a larger data set.

Please Login or Register  to view this content.

**Mike_S** · 03-06-2010, 05:51 PM

Hi Jerry, Very glad that you picked up my thread. You're brilliant, it worked perfectly. In fact when I put in the data set it highlighted errors in the base data. There are so many applications that you can use this for in developing tree structures.

Thanks very much from the land of Oz (hence the delay in responding as have just woken up).

Regards, Mike

**JBeaucaire** · 03-06-2010, 06:12 PM

Well I'm glad it works for you.

And I'm intrigued. Can you share an example of how it highlighted errors? The only formatting I included was intended to match your desired coloring scheme. I'd love to see "helpful" side effects, hehe...

**Mike_S** · 03-06-2010, 07:14 PM

Hi Jerry, Unfortunately cleaned up data set using you macro. However, have included a number of typical errors in data set to highlight where errors can be picked up relatively easily. Refer to output that includes additional columns.
The one error that was not in my original data set and did send it into an infinite loop was if there was a "from-to" (A,C) that was same as a "from-to" (C,A).

Thanks again, Regards, Mike

PS realised that if you run macro then the checks are overwriten. Will move checks into separate sheet and use indirect/offset in future.