Hierarchy

[deutscher]

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!

[rylo]

Hi

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

In a general module, insert the code

Sub aaa()
  Range("D:H").ClearContents
  For Each ce In Range("B2:B11")
    strr = myfunc(ce.Offset(0, -1).Value, Range("B:B")) & "," & ce.Value
  
    arr = Split(strr, ",")
    outrow = Cells(Rows.Count, "D").End(xlUp).Offset(1, 0).Row
    outcol = 4
    For i = LBound(arr) To UBound(arr)
      Cells(outrow, outcol) = arr(i)
      outcol = outcol + 1
    Next i
  Next ce
  
  lastrow = Cells(Rows.Count, "D").End(xlUp).Row
  Range("D2:J" & lastrow).Sort key1:=Range("D2"), order1:=xlAscending, key2:=Range("E2"), order2:=xlAscending
End Sub

Function myfunc(x, rng As Range)
  If WorksheetFunction.CountIf(rng, x) = 0 Then
    myfunc = x
  Else
    Set findit = rng.Find(what:=x)
    myfunc = myfunc(findit.Offset(0, -1), rng) & "," & x
  End If
End Function

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

[deutscher]

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!

[deutscher]

Now it's attached.

Sorry about that.

[rylo]

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

[deutscher]

Thanks again!

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