Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Create Parent Child Relationship From Hierarchy

  1. #1
    Join Date
    7th February 2008
    Posts
    4

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    29th January 2003
    Location
    San Diego Ca
    Posts
    651

    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."

  3. #3
    Join Date
    7th February 2008
    Posts
    4

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    23rd October 2003
    Location
    France Alsace
    Posts
    3,651

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    7th February 2008
    Posts
    4

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    23rd October 2003
    Location
    France Alsace
    Posts
    3,651

    Re: Create Parent Child Relationship From Hierarchy

    VB:
    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    7th February 2008
    Posts
    4

    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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Update Parent Workbook From Child File
    By shippy01 in forum EXCEL HELP
    Replies: 1
    Last Post: May 11th, 2008, 13:28
  2. Align Numbers From 2 Columns In Parent Child Manner
    By ronzander1 in forum EXCEL HELP
    Replies: 5
    Last Post: April 10th, 2008, 06:58
  3. Child & Parent Data Validation List
    By nike_5 in forum EXCEL HELP
    Replies: 8
    Last Post: December 10th, 2007, 19:34
  4. Creating Multiple Child Rows From Parent Sheet
    By tostrander in forum EXCEL HELP
    Replies: 3
    Last Post: October 13th, 2006, 17:15
  5. Updating child sheets from parent sheet
    By pace in forum EXCEL HELP
    Replies: 1
    Last Post: July 1st, 2004, 14:26

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno