Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Create Parent Child Relationship From Hierarchy

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Create Parent Child Relationship From Hierarchy

    I have an excel file (see sample attached) with a hierarchy with parents and children spread across different columns and rows. However, I am trying to condense it into one column with parents and one column with children.

    The only way I've been able to do this is by copying and pasting parents onto children rows, which is both a manual and time-consuming process (when you have a lot of rows). I feel like there must be a way to use VBA or a formula or something to get it to work.

    I have attached a sample, so you can see what I am trying to do (current data and desired data). Any advice or attempts are appreciated.
    Attached Files

  • #2
    Re: Create Parent Child Relationship From Hierarchy

    You should look at grouping and outlining
    its on the tool bar
    Jim
    "The problem with designing vba code completely foolproof is to underestimate the ingenuity of a complete fool."

    Comment


    • #3
      Re: Create Parent Child Relationship From Hierarchy

      I thought about grouping, but this is just a sample file... the real file is about 4000 lines of a hierarchy with 8 different levels. To group everything would probably take the same amount of time as copying and pasting the parent/children. Plus, you can't outline the file in its current state and that wouldn't create a 1:1 parent/child relationship in two separate columns.

      Comment


      • #4
        Re: Create Parent Child Relationship From Hierarchy

        For Child this can be used: =INDEX(C16:IV16,MATCH("*",C16:IV16,-1))
        But for parents (and of course Child) I succeed only with Macro.
        Attached Files
        Triumph without peril brings no glory: Just try

        Comment


        • #5
          Re: Create Parent Child Relationship From Hierarchy

          Thank you so much for your help, PCI! This is exactly what I needed, except I discovered one issue. The issue partly relates to my sample file, but I did not think that this would make a difference in the macro (due to my limited VBA knowledge, I now know that it does).

          I've posted an updated data set with your macro in it; basically, I did not include a description field that is also in the hierarchy. It is probably something as simple as changing the macro to say from right instead of from left or something, but when you run it, the macro pulls the description instead of the correct parent/child relationships.

          This forum is so helpful and I am already increasing my knowledge of Excel and VBA!
          Attached Files

          Comment


          • #6
            Re: Create Parent Child Relationship From Hierarchy

            Code:
            Assuming H is the last column to treat, next macro should run.
            If it's not H you can change it in :
            Last_Col = Cells(I, "H").End(xlToLeft).Column  (  2 times)
            with the right value
            Attached Files
            Triumph without peril brings no glory: Just try

            Comment


            • #7
              Re: Create Parent Child Relationship From Hierarchy

              Great, thank you! This is very helpful. Now that I understand the VBA a little more, I can modify the formula to work for my actual document.

              Comment

              Trending

              Collapse

              There are no results that meet this criteria.

              Working...
              X