Hierarchy

Hello, I could use some help with this. I have 2 columns of data (from Covu) exported to Excel. Column 1 contains all parents (equipment) column 2 contains equipment numbers. The Equipment numbers in the second column can also be parents and appear therefore in both columns. I would like to have the hierarchy over multible columns that I kann see how many levels of parent "child" equipment levels are there.

any help appreciated!

Hi

Say your data is in columns A and B, with A1 and B1 as headings.

In a general module, insert the code

Please Login or Register  to view this content.

I've assumed that the output starts in column D, and for the sake of testing, my data only nested to 4 levels, so I've cleared out columns D:H just to be sure before running.

See how this goes for a start.


rylo

Thank you very much for your help!

The code you made for me is showing me how many levels in the hierarchy and work fine. Yes, I have my data in columns A & B (about 4000 rows). In column a number only appears if it is a parent. Column B has those numbers which are not parents and those which are parents themself. I have attached a screenshot to show a little better what I am looking for.

Thank you!

Now it's attached.

Sorry about that.

Hi

Rather than a word document, can you create a small sample spreadsheet, zip it and attached the zipped file.

Show how both the raw data, and your desired output are structured.


rylo

Thanks again!

Have made a small example as you have requested. The real sheet has about 3500 rows. Thanks very much for your help.

Hi

Try this. I've had to do a couple of things with your data.

1) Make your input and output cells (ie columns A:L) all text (to retain the leading 0s)
2) The highest level in the hierarchy must have a parent not a blank cell. The parent will be the same as the equipment number. So B2 will be the same as C2.
3) I've cut down your sample data to extract a series of entries that have parents (mostly!!!). Enough for the sample only.

Try running this and see if it will work. Again, I've fixed the ranges for the purposes of testing.

rylo

This is great thanks very much! Have changed the ranges as required and run the macro. The result is almost what I was looking for. It does give me the hierarchy of the equipment structure but it has some items not grouped together meaning the parent (for example) "4071" appears in a column again with more of it's "child" equipments. Would it be possible to group those together?
again thnks very much for your help!!

Hi

I've updated the code to add more to the sorting aspects.

Note that this is extremely specific to the example / test data, and may not easily expand to your full data set.

Please Login or Register  to view this content.

rylo

Have inserted the code and modified to suit my application. Everything seems to work fine. The hierarchy and sorting is working well. Thank you very much. Now I will try to understand how your code works.

again thank you very much